Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating an array | New Users to Excel | |||
creating an array | Excel Worksheet Functions | |||
creating an array on the fly | Excel Programming | |||
Creating an Array from a Range using Offset | Excel Programming | |||
Creating an array | Excel Programming |