creating an array from range help
 

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

creating an array from range help
 

Gary Keramidas wrote:
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

creating an array from range help
 

Hi Gary,

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

Debug.Print arr(1,i)

Ken Johnson

creating an array from range help
 

this one worked, thanks ken

Debug.Print arr(i, 1)

--


Gary


"Ken Johnson" wrote in message
ups.com...
Hi Gary,

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

Debug.Print arr(1,i)

Ken Johnson

creating an array from range help
 

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.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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

creating an array from range help
 

thanks tom

--


Gary


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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