Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Acceleration temps calcul en VBA

M

MIKEFLY62

XLDnaute Junior
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
 
Trier par date Trier par votes

JNP

XLDnaute Barbatruc
Re : Acceleration temps calcul en VBA

Bonsoir ,
En simplifiant un peu le code, à tester
Code: 
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
 
Vote positif 0 Vote négatif
M

MIKEFLY62

XLDnaute Junior
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
 
Vote positif 0 Vote négatif

JNP

XLDnaute Barbatruc
Re : Acceleration temps calcul en VBA

Re ,
Ajoute juste à ton code
Code: 
Application.ScreenUpdating = False
en début et
Code: 
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
 
Vote positif 0 Vote négatif
S

smotty

XLDnaute Occasionnel
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
 
Vote positif 0 Vote négatif
M

MIKEFLY62

XLDnaute Junior
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
 
Vote positif 0 Vote négatif

tototiti2008

XLDnaute Barbatruc
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
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

M
  • Question
Aide Code VBA
Réponses
7
Affichages
1 K
MIKEFLY62
M
I
  • Question
Comment afficher un graph., en construction ou modification, seult a la fin?
Réponses
0
Affichages
1 K
Id_forum
I
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…