Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range to array (and back again) (excel xp)

    Good afternoon,

    I've got some code that dumps an array to a range ...
    <pre> Range("B2").Resize(5,5).Value = ArrayVariable</pre>


    This gives me a 5 x 5 range in excel that contains the contents of the array variable. The question is, how do I go from a A x B range to an array variable. I tried ...
    <pre> ArrayVariable = Range("B2").Resize(5,5).Value</pre>

    but it didn't want to work for me.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range to array (and back again) (excel xp)

    What didn't work.
    ArrayVariable should be a 5x5 array of the values from B2:F6

    You could also use directly:
    <pre>ArrayVariable = Range("B2:F6").Value</pre>



    The variable must be dimmed as a variant, not as an array

    <pre>Dim ArrayVariable</pre>



    FYI a generic way to "dump" an array is to use:
    <pre> Worksheets("Sheet1").Range("A1") _
    .Resize(UBound(varray, 1) - LBound(varray, 1) + 1, _
    UBound(varray, 2) - LBound(varray, 2) + 1).Value = vArray</pre>



    Change the sheet, the starting range and array name as desired. The array must be 2-dimensional

    Steve

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range to array (and back again) (excel xp)

    Thanks Steve - I had the variable defined as an array. That was most likely the problem.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range to array (and back again) (excel xp)

    Yes. To read an entire array requires a variant variable. An array variable can only be filled one item at a time.

    Steve

Posting Permissions

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