0

I have written a very straightforward script which writes data into excel cells. Basically, this is a loop over an array and it writes data into specific cells or formulas.

The problem is that this part of the script is extremly slow. Any ideas on how to improve thath?

Thanks.

    For j = 0 To i - 1
    'Insère nouvelle ligne
    Rows(startRow & ":" & startRow).Select
    Selection.Copy
    Rows(startRow + 1 & ":" & startRow + 1).Select
    Selection.Insert Shift:=xlDown

    'Insère données
    If roomType(j) <> "" Then
        Feuil3.Cells(startRow, 1).Value = roomName(j)
        Feuil3.Cells(startRow, 2).Value = roomSurface(j)
        Feuil3.Cells(startRow, 7).Value = roomPeople(j)
        Feuil3.Cells(startRow, 12).Value = roomPeople(j)
        Feuil3.Cells(startRow, 5).Value = dict.Item(roomType(j))
        Feuil3.Cells(startRow, 3).Value = roomHeight(j)

        Feuil3.Range("F" & startRow).Formula = "=IFERROR(IF($E" & startRow & "=Data!$A$55,,ROUNDUP($B" & startRow & "/VLOOKUP($E" & startRow & ",Data!$A$3:$E$55,4,FALSE),0)),)"
        Feuil3.Range("H" & startRow).Formula = "=$C$25"
        Feuil3.Range("I" & startRow).Formula = "=IF($E" & startRow & "=Data!$A$55,$B" & startRow & "*$E$55,(MAX(F" & startRow & ",G" & startRow & ")*H" & startRow & "))"
        Feuil3.Range("N" & startRow).Formula = "=IFERROR(VLOOKUP($K" & startRow & ",$M$22:$O$26,3,FALSE),)"
        Feuil3.Range("O" & startRow).Formula = "=IFERROR(IF(ISBLANK(M" & startRow & ")=TRUE,L" & startRow & "*N" & startRow & ",L" & startRow & "*M" & startRow & "*N" & startRow & "),)"
        Feuil3.Range("Q" & startRow).Formula = "=MAX(I" & startRow & ",O" & startRow & ")"
        Feuil3.Range("T" & startRow).Formula = "=IFERROR(MAX(R" & startRow & ",S" & startRow & ")/(B" & startRow & "*C" & startRow & "),)"
    End If

    startRow = startRow + 1
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Clement
  • 13
  • 3
  • 1
    Selecting, copying and pasting slows down the code remarkably. It is obvious that you should do this in order to duplicate a row and then change some values. How about inserting as much as cells in a seperate loop of your arrays ubound (not insert one row for each loop) , then fill in the values within another loop? – Hakan ERDOGAN May 02 '18 at 07:25

2 Answers2

1

It is quicker to assign an array to a range of cells than to assign to individual cells in a loop. You could try sometihing like: Worksheets("MySheet").Range("A1:D100").Value = myArray. It is because communication between VBA and Excel takes some time. A little bit more on that here: VBA Excel large data manipulation taking forever

Also using Cells instead of Range is aboout 2.6 times faster. Here is a question on that: Range() VS Cells() - run times

Oskar
  • 108
  • 9
  • I tried Worksheets("mySheet").Range("A" & startRow & ":A" & startRow + i).Value = myArray but each cell recieve the value of myArray(0). – Clement May 02 '18 at 08:40
  • Perhaps you should use: `Application.Transpose(myArray)` – Oskar May 02 '18 at 08:50
0

I cut the script in pieces. This part takes most of the time

    For j = 0 To i - 2
    Feuil3.Cells(startRow, 5).Value = dict.Item(roomType(j))
    Feuil3.Cells(startRow, 6).Formula = "=IFERROR(IF($E" & startRow & "=Data!$A$55,,ROUNDUP($B" & startRow & "/VLOOKUP($E" & startRow & ",Data!$A$3:$E$55,4,FALSE),0)),)"
    Feuil3.Cells(startRow, 8).Formula = "=$C$25"
    Feuil3.Cells(startRow, 9).Formula = "=IF($E" & startRow & "=Data!$A$55,$B" & startRow & "*$E$55,(MAX(F" & startRow & ",G" & startRow & ")*H" & startRow & "))"
    Feuil3.Cells(startRow, 14).Formula = "=IFERROR(VLOOKUP($K" & startRow & ",$M$22:$O$26,3,FALSE),)"
    Feuil3.Cells(startRow, 15).Formula = "=IFERROR(IF(ISBLANK(M" & startRow & ")=TRUE,L" & startRow & "*N" & startRow & ",L" & startRow & "*M" & startRow & "*N" & startRow & "),)"
    Feuil3.Cells(startRow, 17).Formula = "=MAX(I" & startRow & ",O" & startRow & ")"
    Feuil3.Cells(startRow, 20).Formula = "=IFERROR(MAX(R" & startRow & ",S" & startRow & ")/(B" & startRow & "*C" & startRow & "),)"

    startRow = startRow + 1
Next j
Clement
  • 13
  • 3