Acceleration temps calcul en VBA

[MIKEFLY62]

Bonjour à tous

Etant sous excel 2010 Beta et utilisant du VBA dont le code est ci dessous ma question est simple :j'aimerais savoir s'il est possible de diminuer le temps de calcul en retouchant un peu ce code sachant que j'execute ceci en calcul manuel.

Merci de votre aide

Voici le code

[Private Sub CommandButton1_Click()

If CommandButton1.BackColor = &HFF& Then
CommandButton1.BackColor = &HFF0000
Else
CommandButton1.BackColor = &HFF&
End If

Sheets("XXXTRA JE DEV+REC").Range("B27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("C27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("D27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("E27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("F27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("G27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15=""""))"

Range(("H27:J27")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("B28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("C28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("D28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("E28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("F28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("G28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15=""""))"

Range(("H28:J28")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("B29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("C29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("D29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("E29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("F29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("G29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15=""""))"

Range(("H29:J29")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("B30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("C30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("D30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("E30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("F30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15=""""))"
Sheets("XXXTRA JE DEV+REC").Range("G30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15=""""))"

Range(("H30:J30")).Calculate

Range(("A31:J31")).Calculate

Range(("A1:J32")).Calculate


Sheets("XXXTRA JE DEV+REC").Range("P27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("Q27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("R27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("S27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("T27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("U27").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12="""")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15<>""""))"

Range(("L27:X27")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("P28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("Q28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("R28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("S28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("T28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("U28").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""D4"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15<>""""))"

Range(("L28:X28")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("P29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("Q29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("R29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("S29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("T29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("U29").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DP"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15<>""""))"

Range(("L29:X29")).Calculate

Sheets("XXXTRA JE DEV+REC").Range("P30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("Q30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("R30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("S30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("T30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R275000C15<>""""))"
Sheets("XXXTRA JE DEV+REC").Range("U30").FormulaR1C1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE'!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12=""DA"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R275000C15<>""""))"

Range(("L30:X30")).Calculate

Range(("L31:X31")).Calculate

End Sub

 

[JNP]

Re : Acceleration temps calcul en VBA

Bonsoir 🙂,
En simplifiant un peu le code, à tester

Private Sub CommandButton1_Click()
Const Formule1 = "=SUMPRODUCT(('BASE'!R2C17:R275000C17=R1C2)*('BASE '!R2C18:R275000C18=R2C2)*('BASE'!R2C12:R275000C12= "
If CommandButton1.BackColor = &HFF& Then
CommandButton1.BackColor = &HFF0000
Else
CommandButton1.BackColor = &HFF&
End If
Application.ScreenUpdating = False
With Sheets("XXXTRA JE DEV+REC")
.Range("B27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C15:R275000C15=""""))"
.Range("C27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R2 75000C15=""""))"
.Range("D27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R2 75000C15=""""))"
.Range("E27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R2 75000C15=""""))"
.Range("F27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R2 75000C15=""""))"
.Range("G27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R2 75000C15=""""))"
.Range("B28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C15:R275000C15=""""))"
.Range("C28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15=""""))"
.Range("D28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15=""""))"
.Range("E28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15=""""))"
.Range("F28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15=""""))"
.Range("G28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15=""""))"
.Range("B29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C15:R275000C15=""""))"
.Range("C29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15=""""))"
.Range("D29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15=""""))"
.Range("E29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15=""""))"
.Range("F29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15=""""))"
.Range("G29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15=""""))"
.Range("B30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C15:R275000C15=""""))"
.Range("C30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15=""""))"
.Range("D30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15=""""))"
.Range("E30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15=""""))"
.Range("F30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15=""""))"
.Range("G30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15=""""))"
.Range("P27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C15:R275000C15<>""""))"
.Range("Q27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15:R2 75000C15<>""""))"
.Range("R27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15:R2 75000C15<>""""))"
.Range("S27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15:R2 75000C15<>""""))"
.Range("T27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15:R2 75000C15<>""""))"
.Range("U27").FormulaR1C1 = Formule1 & """)*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15:R2 75000C15<>""""))"
.Range("P28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C15:R275000C15<>""""))"
.Range("Q28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15<>""""))"
.Range("R28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15<>""""))"
.Range("S28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15<>""""))"
.Range("T28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15<>""""))"
.Range("U28").FormulaR1C1 = Formule1 & "D4"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15<>""""))"
.Range("P29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C15:R275000C15<>""""))"
.Range("Q29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15<>""""))"
.Range("R29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15<>""""))"
.Range("S29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15<>""""))"
.Range("T29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15<>""""))"
.Range("U29").FormulaR1C1 = Formule1 & "DP"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15<>""""))"
.Range("P30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C15:R275000C15<>""""))"
.Range("Q30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=1)*('BASE'!R2C15: R275000C15<>""""))"
.Range("R30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=2)*('BASE'!R2C15: R275000C15<>""""))"
.Range("S30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=3)*('BASE'!R2C15: R275000C15<>""""))"
.Range("T30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=4)*('BASE'!R2C15: R275000C15<>""""))"
.Range("U30").FormulaR1C1 = Formule1 & "DA"")*('BASE'!R2C14:R275000C14=5)*('BASE'!R2C15: R275000C15<>""""))"
End With
Application.ScreenUpdating = True
Application.Calculate
End Sub

Bonne soirée 😎

 

[MIKEFLY62]

Re : Acceleration temps calcul en VBA

Bonjour,

Merci de votre reponse mais

j'ai essayé le code et il buge a l'execution.

N etant pas grand specialiste j'ai essayé de le modifier en vain j essaie encore ....
Pourriez vous voir d'ou cela peut venir pour determiner le gain en terme de temps

merci encore

 

[JNP]

Re : Acceleration temps calcul en VBA

Re 🙂,
Ajoute juste à ton code

Application.ScreenUpdating = False

en début et

Application.ScreenUpdating = True
Application.Calculate

à la fin. Tu verras déjà si ça accélère la chose.
Pour mon code simplifié, il faudrait s'y pencher à tête reposée, ce dont je n'ai pas le temps actuellement 😉...
Bon courage 😎

 

[tototiti2008]

Re : Acceleration temps calcul en VBA

Bonjour MikeFly, Bonjour JNP,

48 SOMMEPROD sur 275000 lignes, je me demande pourquoi c'est lent 😀

 

[smotty]

Re : Acceleration temps calcul en VBA

Bonjour,

Difficile d'être précis sans fichier mais lorsqu'on en arrive à autant de formules, il y a souvent une solution radicalement différente et plus rapide.

Un tableau croisé dynamique en fait souvent partie, quitte à écrire des formules qui lisent dans ce TCD en fonction de certains critères.

A+

smotty

 

[MIKEFLY62]

Re : Acceleration temps calcul en VBA

Bonjour tototiti2008;

tu as raison c'est pas lent, j en suis a 40sec mais je voudrais savoir s'il on peut encore gratter quelques secondes dans le code ou une autre maniere

cordialement

 

[tototiti2008]

Re : Acceleration temps calcul en VBA

Re,
Bonjour smotty

Personnellement je me range à l'idée de smotty, il faudrait que l'on sache pourquoi et dans quelles conditions tu fais tant de calculs pour éventuellement pouvoir te proposer un allègement de la charge pour Excel