2D array values to sheet at specific location

Question

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

You are trying to write to a sheet in a function? With the exception of [This](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet), you can not write to a different cell using a function — Siddharth Rout, May 28 '15 at 19:00
@TimWilliams: You have a long life! I was just thinking of you when I linked one of your posts in my above comment — Siddharth Rout, May 28 '15 at 19:02
Just to note it, your current code will work fine as long as you call it from a `Sub` and not as a UDF. `Sub test(): testArray2Sheet [a1]: End Sub` would work. If you do that, you will also find that your output array is 1 row and column too large. Need to do `.Offset(rows-1, cols-1)`. `Offset` is how many rows to move. Alternatively, you could do `firstCell.Resize(rows, cols).Value = arr` and skip the `lastCell` and `targetRange` business. — Byron Wall, May 28 '15 at 22:05

score 3 · Accepted Answer · answered May 28 '15 at 19:28

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.

2D array values to sheet at specific location

1 Answers1