creating an array from range help

[Gary Keramidas]

i am wondering why this doesn't work:
Sub test()
Dim arr As Variant
Dim i As Long
arr = Range("h2:h11").Value
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i) ' error here
Next
End Sub


but this does:

Sub test()
Dim arr As Variant
Dim i As Long
arr = array("a","b","c")
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub

 

[Ken Johnson]

i am wondering why this doesn't work:
Sub test()
Dim arr As Variant
Dim i As Long
arr = Range("h2:h11").Value
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i) ' error here
Next
End Sub


but this does:

Sub test()
Dim arr As Variant
Dim i As Long
arr = array("a","b","c")
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub
--

how can i access each element when i create the array from a range?

Gary

Hi Gary,

Try Debug.Print arr(i,1) in the first Sub. That type of array is always
2 dim in keeping with the Sheets 2 dim nature of rows and columns.

Ken Johnson

 

[Ken Johnson]

Hi Gary,

and if that doesn't work (I always get confused with these arrays) then
try...

Debug.Print arr(1,i)

Ken Johnson

 

[Gary Keramidas]

this one worked, thanks ken

Debug.Print arr(i, 1)

 

[Tom Ogilvy]

in the first case, the array is always two dimensional:

Sub test()
Dim arr As Variant
Dim i As Long
arr = Range("h2:h11").Value
For i = LBound(arr,1) To UBound(arr,1)
Debug.Print arr(i,1) '<== fix that works
Next
End Sub

The change to Lbound(Arr,1) is for clarity. It would default to that
without the change.

 

[Gary Keramidas]

thanks tom

--


Gary

in the first case, the array is always two dimensional:

Sub test()
Dim arr As Variant
Dim i As Long
arr = Range("h2:h11").Value
For i = LBound(arr,1) To UBound(arr,1)
Debug.Print arr(i,1) '<== fix that works
Next
End Sub

The change to Lbound(Arr,1) is for clarity. It would default to that without
the change.