VBA code won't write array to range, only it's first element

Question

I need to do the following:

• lift the range C2:AU264 into an 2D array
• create another 1D array, (1 To 11880)
• fill second array with values from the first one ("transpose")
• write array 2 back to the sheet

Here is the code I am using:

Private Ws As Worksheet
Private budgets() As Variant
Private arrayToWrite() As Variant
Private lastrow As Long
Private lastcol As Long

Private Sub procedure()
Application.ScreenUpdating = False

Set Ws = Sheet19
Ws.Activate

lastrow = Ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
lastcol = Ws.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

ReDim budgets(1 To lastrow - 1, 1 To lastcol - 2)
budgets= Ws.Range("C2:AU265")

ReDim arrayToWrite(1 To (lastCol - 2) * (lastRow - 1))

k = 0
For j = 1 To UBound(budgets, 2)
    For i = 1 To UBound(budgets, 1)
      arrayToWrite(i + k) = budgets(i, j)
    Next i
    k = k + lastrow - 1
Next j


Set Ws = Sheet6
Ws.Activate

Ws.Range("E2").Resize(UBound(arrayToWrite)).Value = arrayToWrite


'For i = 1 To UBound(arrayToWrite)
    'Ws.Range(Cells(i + 1, 5).Address).Value = arrayToWrite(i)
'Next i

Application.ScreenUpdating = True
End Sub

This just writes the first value from the range C2:AU264 (the first element of the first array) through the whole range E2:E11881. If however, I un-comment the For loop just before the end of my script and do it that way, it does work, but is slow. How can I write the array correctly using the first statement?

Answer 1

If you want to write an array to a range, the array must have two dimensions. Even if you only wish to write a single column.

Change

ReDim arrayToWrite(1 To (lastCol - 2) * (lastRow - 1))

to

ReDim arrayToWrite(1 To (lastCol - 2) * (lastRow - 1), 1 To 1)

and

arrayToWrite(i + k) = budgets(i, j)

to

arrayToWrite(i + k, 1) = budgets(i, j)