Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Array onto worksheet (XP 2002)

    Hi all

    I would like to ask if there is another way to place an array on a worksheet, other than stepping thru it, much the same as a control, like .list=arr?

    Thanks,
    Darryl

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

    Re: Array onto worksheet (XP 2002)

    A one-dimensional array corresponds to a one-row range, so you can do this:

    Dim MyArr(1 To 4)
    MyArr(1) = 37
    MyArr(2) = "Darryl"
    MyArr(3) = #1/1/2009#
    MyArr(4) = True
    Range("A11") = MyArr

    To fill a single column, you must transpose the array:

    Dim MyArr(1 To 4)
    MyArr(1) = 37
    MyArr(2) = "Darryl"
    MyArr(3) = #1/1/2009#
    MyArr(4) = True
    Range("A1:A4") = Application.WorksheetFunction.Transpose(MyArr)

    A two-dimensional array corresponds to a rectangular range:

    Dim MyArr(1 To 2, 1 To 3)
    MyArr(1, 1) = 37
    MyArr(1, 2) = "Darryl"
    MyArr(1, 3) = #1/1/2009#
    MyArr(2, 1) = -15
    MyArr(2, 2) = "Jones"
    MyArr(2, 3) = #1/31/2009#
    Range("A1:C2") = MyArr

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Array onto worksheet (XP 2002)

    Thanks Hans,

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='756272' date='31-Jan-2009 18:49']A two-dimensional array corresponds to a rectangular range:

    Dim MyArr(1 To 2, 1 To 3)
    MyArr(1, 1) = 37
    MyArr(1, 2) = "Darryl"
    MyArr(1, 3) = #1/1/2009#
    MyArr(2, 1) = -15
    MyArr(2, 2) = "Jones"
    MyArr(2, 3) = #1/31/2009#
    Range("A1:C2") = MyArr[/quote]

    Hi Hans,

    2 questions:

    Q1:
    The last bit you do - filling the array into the range
    - is it possible to do that the other way around as well?? (MyArr = Range("A1:C2"))
    (provided that the array is correctly dimensioned in advance)

    and if not...

    Q2:
    Got a dynamic multidimensional range that I need to fill into an array (currently 8 rows by 6 columns, but it may vary).

    Set rngName = wshFocus.Range("Lookup")
    ReDim arrAnaInfo(0 To (iRows - 1), 0 To (iCols - 1))

    For i = 0 To UBound(arrAnaInfo)
    For j = 0 To iCols - 1
    arrAnaInfo(i, j) = 'Need to traverse the named range somehow.
    Next j
    Next i

    How do I get the values from the named range filled into the array?
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please disregard this as I found the solution to my problem.

    Set rngName = shtKeywords.Range("Lookup")

    sTopLeft = rngName.Address
    sTopLeft = Mid(sTopLeft, 1, (InStr(1, sTopLeft, ":", vbTextCompare)) - 1)

    iRows = rngName.Rows.Count
    iCols = rngName.Columns.Count

    ReDim arrAnaInfo(0 To (iRows - 1), 0 To (iCols - 1))

    For i = 0 To UBound(arrAnaInfo)
    For j = 0 To iCols - 1
    arrAnaInfo(i, j) = Range(sTopLeft).Offset(i, j).Value
    Next j
    Next i
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  6. #6
    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
    [quote name='Henrik Ryberg' post='765247' date='13-Mar-2009 15:03']Please disregard this as I found the solution to my problem.

    Set rngName = shtKeywords.Range("Lookup")

    sTopLeft = rngName.Address
    sTopLeft = Mid(sTopLeft, 1, (InStr(1, sTopLeft, ":", vbTextCompare)) - 1)

    iRows = rngName.Rows.Count
    iCols = rngName.Columns.Count

    ReDim arrAnaInfo(0 To (iRows - 1), 0 To (iCols - 1))

    For i = 0 To UBound(arrAnaInfo)
    For j = 0 To iCols - 1
    arrAnaInfo(i, j) = Range(sTopLeft).Offset(i, j).Value
    Next j
    Next i[/quote]
    If you use a Variant, or Variant array, you can just assign it directly:
    Code:
    Set rngName = shtKeywords.Range("Lookup")
    varData = rngName.Value
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory for pointing out to me, how I have wasted time during the past years <MAJOR BIG GRIN - BANG HEAD>

    So easy and obvious once you know. I promise - I will never forget this method!!

    THX
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  8. #8
    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
    [quote name='Henrik Ryberg' post='765750' date='17-Mar-2009 08:19']Thanks Rory for pointing out to me, how I have wasted time during the past years <MAJOR BIG GRIN - BANG HEAD>

    So easy and obvious once you know. I promise - I will never forget this method!!

    THX[/quote]

    Glad to help!
    Just be aware that even if you use a one row or one column range, your array will always be 2-dimensional.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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