# Thread: Array onto worksheet (XP 2002)

1. ## 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. ## 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. ## Re: Array onto worksheet (XP 2002)

Thanks Hans,

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

5. Please disregard this as I found the solution to my problem.

Set rngName = shtKeywords.Range("Lookup")

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

6. [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 = 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```

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

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