Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Element (03)

    I am having a challenge displaying the elements in an array.

    Dim myArray

    myArray = Range("MyRange")
    For x = 0 to UBound(myArray)
    Msgbox myArray(x)
    Next

    Thanks,
    John

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Element (03)

    If you assign a range to a variant that way, you get an array that is 1 based - i.e. the lower bound is 1 not 0. Try replacing <code>For x = 0 to UBound(myArray)</code>
    with either:
    <code>For x = 1 to UBound(myArray)</code>

    or
    <code>For x = LBound(myArray) to UBound(myArray)</code>

    Edited to add: I just noticed that you have only used one dimension in your Msgbox statement. A range passed to a variant creates a two-dimensional array so you need to use something like:
    <code>Msgbox myArray(x, 1)</code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Element (03)

    Further to my last post, if you don't know how many columns you have and you want to iterate through all values, you would use something like:
    <pre> Dim myArray, x As Long, y As Long
    myArray = Range("MyRange")
    For x = LBound(myArray, 1) To UBound(myArray, 1)
    For y = LBound(myArray, 2) To UBound(myArray, 2)
    MsgBox myArray(x, y)
    Next y
    Next x

    </pre>


    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Element (03)

    Rory,

    Thanks for the explanation. It worked.

    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Element (03)

    Added: oops, I was interrupted while replying, so I didn't noticed this had already been resolved.

    A range is a two-dimensional array, even if it consists of only one row or column. And better use LBound too.

    Dim myArray
    Dim x As Integer
    Dim y As Integer
    myArray = Range("MyRange")
    For x = LBound(myArray, 1) To UBound(myArray, 1)
    For y = LBound(myArray, 2) To UBound(myArray, 2)
    MsgBox myArray(x, y)
    Next y
    Next x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •