How do I populate a column from an array?

Question

Using Excel VBA I'm trying to populate a small set of the cells in my newly created worksheet. When I use the following code:

Sub CreateFormulaDataSheet()
    Dim currentWs As Worksheet
    Dim formWs As Worksheet
    Dim titles As String
    Dim valuesArr As Variant

    If Not SheetExists("FormulaData") Then
    'create new sheet
    Set currentWs = ActiveWorkbook.ActiveSheet
    With ActiveWorkbook
        Set formWs = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        formWs.Name = "FormulaData"

        formWs.Activate

        'populate with default values
        valuesArr = Array(1, 3, 6)

        Range(Cells(4, 1), Cells(6, 1)).Value = valuesArr

    End With
End Sub

What I get is this:

What I want is this:

Why is the Range function only populating the first element in the array?

I know this is only a few cells to update so I could easily set the value of each one individually, but I want to understand why the code I have doesn't work and what the solution is so that next time when I have 50 cells to update, I won't have to have 50 individual cell assignments. ;-)

Answer 1

You're looking for a transpose method:

Sub CreateFormulaDataSheet()
    Dim currentWs As Worksheet
    Dim formWs As Worksheet
    Dim titles As String
    Dim valuesArr As Variant

    If Not SheetExists("FormulaData") Then
    'create new sheet
    Set currentWs = ActiveWorkbook.ActiveSheet
    With ActiveWorkbook
        Set formWs = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        formWs.Name = "FormulaData"

        formWs.Activate

        'populate with default values
        valuesArr = Array(1, 3, 6)

        Range("A4").Resize(UBound(valuesArr) + 1, 1).Value = Application.Transpose(valuesArr)

    End With
    End If
End Sub  

To keep it simple, imagine that 1 dimention arrays are stored horizontaly. So same as for data on a worksheet, if you want to assign it to a vertical range, you have to transpose it first.

Second thing I used is the Resize() method which will conveniently extant your destination range according to your array size.

Answer 2

In cases Transpose is a limitation, populate a 2D array ahead and directly dump on sheet.

Sub test()

  ReDim valuesArr(0 To 2, 0 To 0)

  valuesArr(0, 0) = 1
  valuesArr(1, 0) = 3
  valuesArr(2, 0) = 6

  Range("A4:A" & UBound(valuesArr, 1) + 4) = valuesArr

End Sub