PDA

View Full Version : [SOLVED] Filling an Array works only with certain ranges...why?



compariniaa
07-25-2006, 01:39 PM
I'm trying to populate an array with 119 values from a range. The range is in O62:O181. When I do a


for r = 62 to 181
loop, I get a "Subscript out of Range" error
however, when I change it to


For r = 1 to 119


there's no problem. Furthermore, neither of these work if the proper sheet is not selected.

I've included a workbook with dummy data and my affected procedure. Any help is much appreciated

compariniaa
07-25-2006, 02:08 PM
thanks to anyone who took a look. turns out i did the math wrong and should have made my array 120 items instead of 119

lucas
07-25-2006, 06:05 PM
Thanks for posting your solution.

Aaron Blood
07-26-2006, 06:09 AM
OK... I'll ask the question.

Why are you stepping thru the cells of a range to assign the values to an array? A range IS an array.

If you dim your array as a variant you don't even have to specify upper and lower bounds. As a bonus, I'll throw in the fact that you can dump arrays straight to a cell and automatically size the output range to fit the array structure.



Dim MyArray As Variant

Sub LoadArray()
MyArray = Range("O62:O181").Value
End Sub

Sub DumpArray()
Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)).Value = MyArray
End Sub

compariniaa
07-26-2006, 07:49 AM
I had no idea....thanks Aaron!