Hello,
I have the weirdest problem which I can't crack. I need to do the following:
Here is the code I am using:
This just writes the first value from the range C2:AU264 (the first element of the first array) through the whole range. Ih however, I un-comment the for loop just before the end of my script and do it that way, it does work. It takes awful lot of time, like couople of minutes or so.
What am I doing wrong? Thanks!
I have the weirdest problem which I can't crack. I need to do the following:
- lift the range C2:AU264 into an 2D array
- create another 1D array
- fill second array with values from the first one ("transpose"
- write array 2 back to the sheet
Here is the code I am using:
Code:
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. Ih however, I un-comment the for loop just before the end of my script and do it that way, it does work. It takes awful lot of time, like couople of minutes or so.
What am I doing wrong? Thanks!