I really need help on this issue . Is there's anyway i can store a range("A1:G1") to an array? if there's a way to do it, can you show me the codes?
Thanks a million
I really need help on this issue . Is there's anyway i can store a range("A1:G1") to an array? if there's a way to do it, can you show me the codes?
Thanks a million
Hello faye,
Here is some code that will do that:Originally Posted by faye
[VBA]
Sub Range2Array()
Dim aYAry() As String 'Your array
Dim rYRng As Range ' Your range
Dim rCell As Range
Set rYRng = Worksheets(1).Range("A1:G1")
ReDim aYAry(rYRng.Rows.Count, rYRng.Columns.Count)
For Each rCell In rYRng
aYAry(rCell.Row - rYRng.Row + 1, rCell.Column - rYRng.Column + 1) = rCell.Value
Next rCell
End Sub[/VBA]
Rembo
If you want a one dimensional array, you'll need to add each item individually[VBA]Dim arrMyRange(1 To 7)
Dim i As Long
For i = 1 To 7
arrMyRange(i) = Cells(1, i).Value
Next[/VBA]
or you can assign the range to a Variant - that will result in a 2 dimensional variant array where the rows/column indexes of the range correspond to the array elements (i.e. row1 col 1 = array element(1,1), row1 col 2 = array element(1,2), etc)[VBA]Dim arrMyRange
arrMyRange = Range("A1:G1")[/VBA]
K :-)
Hey, it worked! Thanks for the help!