Array question

[Greg Snidow]

Greetings all. I have read many posts about using arrays in a macro, but I
can't seem to find one elementary enough to suit my needs. I have never used
them, so I need basic stuff. Lets say I have three columns, "Col1", "Col2",
and "Col3", and in those columns I have 1, 2, 3, on row 2, and I have 4, 5, 6
on row three, and so forth, basically just a table of numbers. Is it
possible to use an array to temporarily store the table, then write it to
another sheet? Is there a good place to go for learning how to use them?
Thank you.

Greg

 

[OssieMac]

Hi Greg,

This answer also on your other post.

Hi Greg,

In the following example it assigns the range values to an array then uses
msgbox to read the values from the 2 dimensional array.

Ensure you use ".Value" on the end of the range when assigning it or it does
not work.

Sub RangeToArray()

Dim arrMyArray()
Dim i As Long
Dim j As Long

With Sheets("Sheet1")
arrMyArray = .Range("A1:C10").Value
End With

'Number elements down (first dimension)
For i = 1 To UBound(arrMyArray)

'Number elements across (second dimension)
For j = 1 To UBound(arrMyArray, 2)
MsgBox arrMyArray(i, j)
Next j

Next i

End Sub

Also Google (or you favourite search engine) "excel vba array tutorial" for
more information