Private Sub CommandButton1_Click()
Dim t#, plage1 As Range, plage2 As Range, tablo(999, 2), i%, j%, F1$, F2$, n%, maxi#
t = Timer 'pour calculer la durée d'exécution
Application.ScreenUpdating = False
Set plage1 = Range("D120:D" & [D65536].End(xlUp).Row)
Set plage2 = Range("G120:G" & [D65536].End(xlUp).Row)
For i = 1 To 100
F1 = "=MAX(A" & i & ":A120)"
[D120].Formula = F1
[D120].AutoFill plage1
For j = 1 To 10
F2 = "=IF(E120=2,IF(AND(A120>A" & 120 + j & ",A120>A" & 121 + j & "),1,-1),"""")"
[G120].Formula = F2
[G120].AutoFill plage2
If IsNumeric([E104]) Then tablo(n, 0) = [E104]
tablo(n, 1) = F1
tablo(n, 2) = F2
n = n + 1
Next
Next
maxi = Application.Max(tablo)
F1 = Application.VLookup(maxi, tablo, 2, 0)
F2 = Application.VLookup(maxi, tablo, 3, 0)
[D120].Formula = F1
[G120].Formula = F2
[D120].AutoFill plage1
[G120].AutoFill plage2
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.0") & " s"
End Sub