creating an array from range help

G

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
 
K

Ken Johnson

Gary said:
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
 
K

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
 
T

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.
 
G

Gary Keramidas

thanks tom

--


Gary


Tom Ogilvy said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top