CODEWITHSUNDEEP
arraysvbaexcelrange
Bjorn Mistiaen
Bjorn Mistiaen

Reputation: 6865

2D array values to sheet at specific location

I'm trying to get the values of a 2D array onto a worksheet, starting at a certain cell.
For example, in cell A1, I type the formula =testArray2Sheet(D7)
The expected outcome would be that the values appear on the sheet, the first value being in cell D7 and spanning 18 rows down and 3 columns across.

My current code just stops executing at this line: targetRange.Value = arr and exits without throwing a warning or an error.
Cell A1 just says #VALUE.
I have no idea why though...

Function testArray2Sheet(firstCell As range)
    Dim ret As Boolean 'dummy return value
    Dim targetRange As range
    Dim lastCell As range
    Dim arr As Variant
    Dim rows, cols As Integer
    Dim i, j As Integer

    'Determine size of array
    rows = 18
    cols = 3

    'Make sure the array has the new dimensions
    ReDim arr(1 To rows, 1 To cols)

    'Fill the array with values
    For i = 1 To 18
    For j = 1 To 3
        arr(i, j) = i * j
    Next
    Next

    'firstCell is the top-left corner of the targetRange
    'Now determine the bottom-right corner of the targetRange
    Set lastCell = firstCell.Offset(rows, cols)

    'Create the targetRange
    Set targetRange = range(firstCell, lastCell)

    'Put the values of the array to the targetRange
    'This should me the values appear in the worksheet
    targetRange.Value = arr

    'Return a dummy value, because a function needs to return something
    testArray2Sheet = ret
End Function

Upvotes: 1

Views: 192

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

To move an array into the sheet, place the array formula (UDF) in the cells you want to receive the values. Say the UDF is:

Function testArray2Sheet()
    Dim targetRange As Range
    Dim arr As Variant
    Dim rows As Long, cols As Long
    Dim i As Long, J as Long

    rows = 18
    cols = 3

    'Make sure the array has the new dimensions
    ReDim arr(1 To rows, 1 To cols)


    For i = 1 To 18
      For j = 1 To 3
          arr(i, j) = i * j
      Next
    Next

    testArray2Sheet = arr
End Function

First hi-light a block of cells, say cells B5 through D22.
Then click in the Formula Bar and enter the array formula:

=testArray2Sheet()

(using Ctrl + Shift + Enter rather than just the Enter key)

and you should see:

enter image description here

It is the block of cells in which the formula is entered that determines the destination of the array.

Upvotes: 3

Related Questions