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

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

Gary

[Ken Johnson]

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

[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)

--


Gary


"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi Gary,
>
> and if that doesn't work (I always get confused with these arrays) then
> try...
>
> Debug.Print arr(1,i)
>
> Ken Johnson
>

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

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
>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
>
>
>

[Gary Keramidas]

thanks tom

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
>>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
>>
>>
>>
>
>