Populate a multi-dimensional array from Ranges
Peter,
Thank you very much for your response.
I want to end up with an array called newArray(1 to numRows, 0 to 3)
With
newArray(1,0) = worksheets(All_data).Cells(6,6).value value in F6.
newArray(1,1) = worksheets(All_data).Cells(6,12).value value in L6 etc.
Suggestion 1 does not work for me.
Dim arr As Variant
Dim MyRange As Range
Set MyRange = Range(Worksheets("All_Data").Cells(6, 6), _
Worksheets("All_Data").Cells(1000, 6))
arr = myRange does not work for me
however,
arr = application.transpose(myRange) works but I dont know why.
Suggestion 2 is also giving me trouble
Dim multiArr(1 to 3) As Variant ?
multiArr(I) = .Range("F6:F10000").Value
How would I loop through this to verify the data?
Suggestion 3 looks like it should work, but it is not working either.
Thanks,
Raul
"Peter T" wrote:
I don't follow all of what you are trying to do.
You can assign values in a single area range to an array in one go like
this -
Dim arr as Variant
arr = myRange
arr() will now be a two-D array populated with all the values in the range,
and sized like this (even if myRange is only one cell)
(1 to myRange.Rows.Count, 1 to myRange.Columns.Count)
If you want to populate a single 3D array with values from a multiple area
range -
Dim multiArr(1 to 3)
multiArr(I) = .Range("F6:F10000").Value
'etc
x = multiArr(1,1,1) ' value in F6
Alternatively, to end up with a single 2D array try something like this
(noting in your example all 3 ranges are single columns)
cnt = rng(1).rows.count + rng2.rows.count + rng3.rows.count
Set bigRng = union(rng1, rng2, rng3)
ReDim bigArray(1 to cnt, 1 to 1) ' < change the 1 to 1 as necessary
n = 0
For Each rArea in bigRng
arr = rArea.Value
For i = 1 to UBound(arr)
n = n + 1
bigArray(n,1) = arr(i)
Next
Next
Regards,
Peter T
"Raul" wrote in message
...
I need to know if a multi-dimensional array can be populated directly from
non-contiguous ranges in a worksheet?
I can do the following but it gets pretty slow when the number of rows
exceeds 20000.
For i = 1 to 10000
NewArray(i,0) = Worksheets("All_Data").Cells(5 + i, 6)).Value
NewArray(i,1) = Worksheets("All_Data").Cells(5 + i, 12)).Value
NewArray(i,2) = Worksheets("All_Data").Cells(5 + i, 22)).Value
Next i
I can also populate one-dimensional arrays directly from a range and then
use a For Next Loop to populate NewArray(1 to 10000, 0 to 2)
DateArray =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("F6:F10000"))
DataArray1 =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("L6:L10000"))
DataArray2 =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("V6:V10000"))
For i = 1 to 10000
NewArray(i,0) = DateArray(i)
NewArray(i,1) = DataArray1(i)
NewArray(i,2) = DataArray2(i)
Next i
But I'd like to be able to use something on the order of:
Application.Index(NewArray, 0, 1) =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("L6:L1000"))
Or
Application.Index(NewArray, 0, 1) = DataArray1
Is this possible? If it is, can you give me an example?
Thanks in advance,
Raul
|