Beginners' Question : How to move selected columns & rows to a two dimensional array

[Erche]

Hi everybody,
I have a case.
Description :

A B
1.2 apple
1.3 yoghurt
1.1 mango
1.0 papaya

how to move them into a array ?

that's all for now

Thanks in advance

 

[Mike]

Dim MyArray As Variant
ReDim MyArray(4, 2)
MyArray = Range("A1:B4").Value

Just make sure you predefine your array to fit. And note
that when you do this, MyArray(1,1) will contain the first
piece of data, not MyArray(0,0).

 

[Erche DP]

Hi Mike,
Thanks a lot.
The idea is to have for example :
1.3 is the maximum value on column A
so, i want to have the value on column B which the same row as the 1.3
(which is yoghurt)
the LOOKUP method didn't help, because it should have those value sorted
first, while i can not have my real report sorted.
so, what do you suggest ?

A B
1.2 apple
1.3 yoghurt
1.1 mango
1.0 papaya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Alan Beban]

One way is the following; it sorts, extracts the array, then resorts:

Sub testab2001()
Dim arr(), rng As Range, iRows As Long, i As Long
Set rng = Range("A1:B4")
iRows = rng.Rows.Count
For i = 1 To iRows
rng(i, 3).Value = i
Next
x = Range(rng(1, 1), rng(iRows, 3)).Sort(rng(1, 1), xlDescending)
arr = rng
x = Range(rng(1, 1), rng(iRows, 3)).Sort(rng(1, 3), xlAscending)
Range(rng(1, 3), rng(iRows, 3)).ClearContents
End Sub

Alan Beban

 

[Erche DP]

Hi Alan,
thanks for the reply,
now, i have the idea to solve this..
but, somehow i still have problem with Range method..

rangeRng = "d4:h34"
arr = Range(rangeRng).Value

this works
but,

shName = "nmJul"
refTabMonthX = ActiveWorkbook.Names(shName).RefersToRange.Column
refTabMonthY = ActiveWorkbook.Names(shName).RefersToRange.Row
startRange = refTabMonthX
endRange = refTabMonthY + 30

rangeRng = startRange & ":" & endRange
arr = Range(rangeRng).Value

does not work,

1) i tried to get output by msgbox arr(1,1)
getting empty string.. that goes for any cells in that array.

2) however, all i want is to select the range for a several times, with
different range each iteration
for example,
i=1 ; range = d4 : e34
i=2 ; range = g4 : h34

somehow, the range (startrange, endrange) can not be added.. (d4+5 = h4)
...

so, please advise..
Thank you....

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!