Optimize for loop in order to win time

[DamienR]

Dear all,

i am working on a huge excel document (10000 rows more than 80 columns)
I code a macro to reduce the size of the document, and it is working, the problem is that my macro is really slow to update all the datas, and i don't know (yet) how to optimize my code to win some time.

The document is quiet sensitiveBackgroung For loop optimizing.xlsx so i will just give you my code and a sample of the doc.

Sub Sumiftest()

Dim j As Integer
Dim i As Integer


'define the range where you want to update the datas i = depart row To end row
' S,B and OPO are different sheet of the workbook

For i = 1 To 10

Cells(i, 8).Select

'first 8 columns are define by a formula in each cells so here i offset after each cell
Cells(i, 8).Value = WorksheetFunction.SumIf(Range("'S'!H:H"), Cells(i, 5).Value, Range("'S'!L:L"))
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = WorksheetFunction.SumIf(Range("'S'!H:H"), Cells(i, 5).Value, Range("'S'!K:K"))
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = WorksheetFunction.SumIfs(Range("'B'!G:G"), Range("'B'!C:C"), Cells(i, 5))
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = WorksheetFunction.SumIf(Range("'OPO'!D:D"), Cells(i, 5).Value, Range("'OPO'!N:N"))
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = WorksheetFunction.SumIf(Range("'S'!H:H"), Cells(i, 5).Value, Range("'S'!G:G"))
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Range("H" & i) + Range("K" & i) - Range("I" & i) - Range("J" & i)
ActiveCell.Offset(0, 1).Select

' here, i have a problem with WorksheetFunction.Max( where i have a 1004 error, "unable the get max function of etc.."
ActiveCell.Value = Range("H" & i) + Range("K" & i) - Range("I" & i) - WorksheetFunction.Average(WorksheetFunction.Max(Range("AT" & i) + Range("AU" & i), Range("AV" & i)), WorksheetFunction.Max(Range("AW" & i) + Range("AX" & i), Range("AY" & i))) / 26 * Cells(i, 12)
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Range("H" & i) - Range("J" & i)
ActiveCell.Offset(0, 1).Select

' the 78 other columns are define by the same formula so here i made a loop on column
For j = 1 To 78
ActiveCell.Value = WorksheetFunction.SumIf(Range("'B'!C:C"), Cells(i, 5).Value, Range("'B'!G1").Offset(0, j).EntireColumn)
ActiveCell.Offset(0, 1).Select
Next j

' those line are here because in the file one row upon two are used. And sometimes two rows are blanked.
If IsEmpty(Cells(i, 5)) = False Then
i = i + 1
End If

If IsEmpty(Cells(i, 5)) = False Then
i = i
End If

'finish to work on the line
Next i

End Sub

The code is working ( when the 1004 error doesn t want to show up) - please believe me.
If someone as any suggestion to win time, please don't hesitate to answer.

Thanks

[jason_lee_91]

Use relative references to make all your formulas in one go.

You have a whole bunch of something like

Range("A1").select
Activecell.value = "=A2+A3"
Activecell.offset(0,1).select
Activecell.value = "B2+B3"

Instead you should do something like

Range("A1:B1").formulaR1C1 = "R2C+R3C"