PDA

View Full Version : Solved: Array to store range



faye
11-27-2005, 10:07 PM
I really need help on this issue: pray2: . 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

Rembo
11-28-2005, 05:07 AM
Hello faye,


I really need help on this issue: pray2: . Is there's anyway i can store a range("A1:G1") to an array?


Here is some code that will do that:

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

Rembo

Killian
11-28-2005, 07:14 AM
If you want a one dimensional array, you'll need to add each item individuallyDim arrMyRange(1 To 7)
Dim i As Long

For i = 1 To 7
arrMyRange(i) = Cells(1, i).Value
Next
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)Dim arrMyRange

arrMyRange = Range("A1:G1")

faye
11-28-2005, 11:04 PM
Hey, it worked! Thanks for the help!