Printable View
Is there a way to set the indices of an array equal to the values of cells in a range? Like this:
Dim myArray(1 to 2, 1 to 5) As Long
myArray = Range(Cells(1,1), Cells(2,5)).Value
I can't get the above code to work. I can easily go the other way:
Range(Cells(1,1), Cells(2,5)).Value = myArray
Is there a trick to it, other than simply looping through each cell and storing the value in the corresponding array index? Any help would be appreciated.
No, no shortcut.
Since your array is a long you may not want to depend on the default values
for the cells. Explicitly code out the entire objects and then convert them to
a long to match your array.
The As Long declaration was arbitrary. The actual application in which I wish to use this concept contains only numerical values that will be treated as double precision.
I just discovered that the following does work:
Dim myArray as Variant
Range(Cells(1, 1), Cells(2, 5)).Value = 123
myArray = Range(Cells(1, 1), Cells(2, 5)).Value
Range(Cells(3, 1), Cells(4, 5)).Value = myArray
After executing the above code the values in the first range are rewritten into the second range. Unfortunately this doesn't work if I dimension the myArray variable as an array, even if the dimensions of the array exactly match the size of the range. I don't understand this behavior. Why would it work in one direction (dimensioned array to range) but not in the other (range to dimensioned array)? I guess it's just one of those things. :confused: