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?

I think the length of range is different than what you're writing. I believe it should be Resize(UBound(arrayToWrite)+1). — Charlie
– Charlie, Commented May 18, 2016 at 16:28
Charlie, I don't think that is the problem. Isn't "normal" VB behavior in sich a case that everything after the boundary gets cut off? I that case I would only lose the last value but I can't get anything! Just the first valueof array budget(1,1) gets written through all the rows — Alexander Starbuck
– Alexander Starbuck, Commented May 18, 2016 at 16:30
I agree - I knew there was some issue if the range doesn't match the array - looks like someone caught it below. — Charlie
– Charlie, Commented May 18, 2016 at 16:41

Dirk Reichel · Accepted Answer · 2016-05-18 17:02:35Z

5

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)

edited May 18, 2016 at 17:02

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

answered May 18, 2016 at 16:35

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dirk Reichel Over a year ago

you should note that writing in a single row, having a only one dimension works. [A1:B1] = Array(1, 2) does exactly what it looks like ;)

Dirk Reichel · Accepted Answer · 2016-05-18 17:21:29Z

2

simply use transpose... change

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

to

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

Hint: there is no need for ReDim budgets(1 To lastrow - 1, 1 To lastcol - 2).
If budgets is a variant then budgets = Ws.Range("C2:AU265") will automatically set the ranges (upper left cell (in this case C2) will be (1, 1)).

EDIT

Assuming you only want to write down all columns (one after another) below each other, you can shorten the macro a bit like that:

Private Sub procedure()

  Dim inArr As Variant, outArr() As Variant
  Dim i As Long, j As Long, k As Long

  With Sheet19
    .Activate
    inArr = .Range(, .Cells(2, 3), .Cells(.Cells.Find("*", , , , 1, 2).Row, .Cells.Find("*", , , , 2, 2).Column)).Value
  End With

  ReDim outArr(1 To UBound(inArr) * UBound(inArr, 2))
  k = 1

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

  Sheet6.Range("E2:E" & UBound(arrayToWrite)).Value = Application.Transpose(arrayToWrite)

End Sub

And if you want each row transposed and below each other than simply switch the two For...-lines. (Still the code does basically the same like before)

edited May 18, 2016 at 17:21

answered May 18, 2016 at 16:52

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

3 Comments

Alexander Starbuck Over a year ago

Dirk, thank you for your reply! I can't believe how stupid I was - the arrayToWrite "stretches" in the direction of rows and not columns! That's why It kept getting only the first value. I understand your solution, but also, could I have done it by assigning values to this array differently in the first place? ( arrayToWrite(i + k) = budgets(i, j) )

Dirk Reichel Over a year ago

no... a 1d-array will always "stretch" by columns (1 row height). To use it without transpose, you need a second dimension like @jkpieterse showed pretty well ;)

Alexander Starbuck Over a year ago

In that case we misunderstood each other. I thought it stretched by columns, thats why I sorted my code like that. Ok thanks ;)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related