Optimiser code qui agit sur beaucoup(trop mais choix par défaut)de lignes

Le 08/02/2023 à 17:35

Bonjour à toutes et tous,

J'ai un macro code VBA créé via l'enregistreur de macro puis que j'ai adapté.

Cette macro agit sur un très grand nombre de ligne (+ de 60 000) sur base de donnée à partir de laquelle j'exécute différents calculs.

J'imagine que dans ce cas une approche via POWER QUERRY ou POWERBI serait peut-être plus approprié mais je n'ai pas suffisamment de connaissance pour répondre aujourd"hui à mon besoin et qui plus est je travaille sur un MAC.

A votre avis est-il possible d'optimiser mon code, je fait surtout référence à Sub MAJTABMOIS pour l'accélérer malgré le trés nombre de ligne à traiter.

Le Timer est optionnel le temps du développement je l'enlèverais dans ma version finale.

Merci

Trés cordialement

Hugues

Option Explicit

Sub MAJTABJOUR()
'
' mettre à jour TABJOUR

timerDebut = Timer

Sheets("TABJOUR").Activate

Range("TABJOUR[[TYPE]:[EXCERCICE]]").ClearContents

    Range("N2").Select
    ActiveCell.Formula2R1C1 = _
        "=XLOOKUP([@ANNEE]&[@MOIS],TABEXERCICE[ANNEE]&TABEXERCICE[MOIS],TABEXERCICE[EXERCICE],""ns"")"

      Calculate

    Range("TABJOUR[[TYPE]:[EXCERCICE]]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    Range("A1").Select

MsgBox "Durée : " & (Timer - timerDebut) & " sec."

End Sub

Sub MAJTABMOIS()
'
' mettre à jour TABMOIS

Dim timerDebut As Double

timerDebut = Timer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("TABMOIS").Activate

 Range("TABMOIS[[CA_NET_TTC_TOTAL]:[QTE 2023]]").ClearContents

    Range("E2").Formula2R1C1 = _
        "=SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[ANNEE]*1=RC3)*(TABJOUR[MOIS]*1=RC4)*IF(ISNUMBER(TABJOUR[CA_NET_TTC_TOTAL]),TABJOUR[CA_NET_TTC_TOTAL]))"

    Range("F2").Formula2R1C1 = _
        "=SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[ANNEE]*1=RC3)*(TABJOUR[MOIS]*1=RC4)*IF(ISNUMBER(TABJOUR[OBJECTIF_CA_TTC]),TABJOUR[OBJECTIF_CA_TTC]))"

    Range("G2").Formula2R1C1 = _
        "=SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[ANNEE]*1=RC3)*(TABJOUR[MOIS]*1=RC4)*IF(ISNUMBER(TABJOUR[FREQUENTATION]),TABJOUR[FREQUENTATION]))"

    Range("H2").Formula2R1C1 = _
        "=SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[ANNEE]*1=RC3)*(TABJOUR[MOIS]*1=RC4)*TABJOUR[CLIENT])"

    Range("I2").Formula2R1C1 = _
        "=SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[ANNEE]*1=RC3)*(TABJOUR[MOIS]*1=RC4)*TABJOUR[QTE TOTAL])"

    Range("J2").Formula2R1C1 = _
        "=SUMPRODUCT(('OBJECTIFS INITIAUX'!R1C4:R1C18=[@MAGASIN])*('OBJECTIFS INITIAUX'!R3C1:R14C1=[@ANNEE])*('OBJECTIFS INITIAUX'!R3C2:R14C2=[@MOIS])*'OBJECTIFS INITIAUX'!R3C4:R14C18)"

    Range("L2").Formula2R1C1 = _
        "=XLOOKUP([@ANNEE]&[@MOIS],TABEXERCICE[ANNEE]&TABEXERCICE[MOIS],TABEXERCICE[EXERCICE],""ns"")"

    Range("M2").FormulaR1C1 = _
        "=IF([@MOIS]=4,1,IF([@MOIS]=5,2,IF([@MOIS]=6,3,IF([@MOIS]=7,4,IF([@MOIS]=8,5,IF([@MOIS]=9,6,IF([@MOIS]=10,7,IF([@MOIS]=11,8,IF([@MOIS]=12,9,IF([@MOIS]=1,10,IF([@MOIS]=2,11,IF([@MOIS]=3,12))))))))))))"

    Range("N2").FormulaR1C1 = "=TEXT([@MOIS]*29,""mmmm"")"

    Range("O2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2018"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2018"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("P2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2019"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2019"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("Q2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2020"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2020"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("R2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2021"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2021"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("S2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2022"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2022"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("T2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2023"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2023"")*TABJOUR[CA_NET_TTC_TOTAL]),0)"

    Range("U2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2018"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2018"")*TABJOUR[FREQUENTATION]),0)"

    Range("V2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2018"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2018"")*TABJOUR[CLIENT]),0)"

    Range("W2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2018"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2018"")*TABJOUR[QTE TOTAL]),0)"

    Range("X2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2019"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2019"")*TABJOUR[FREQUENTATION]),0)"

    Range("Y2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2019"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2019"")*TABJOUR[CLIENT]),0)"

    Range("Z2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2019"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2019"")*TABJOUR[QTE TOTAL]),0)"

    Range("AA2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2020"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2020"")*TABJOUR[FREQUENTATION]),0)"

    Range("AB2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2020"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2020"")*TABJOUR[CLIENT]),0)"

    Range("AC2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2020"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2020"")*TABJOUR[QTE TOTAL]),0)"

    Range("AD2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2021"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2021"")*TABJOUR[FREQUENTATION]),0)"

    Range("AE2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2021"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2021"")*TABJOUR[CLIENT]),0)"

    Range("AF2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2021"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2021"")*TABJOUR[QTE TOTAL]),0)"

    Range("AG2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2022"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2022"")*TABJOUR[FREQUENTATION]),0)"

    Range("AH2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2022"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2022"")*TABJOUR[CLIENT]),0)"

    Range("AI2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2022"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2022"")*TABJOUR[QTE TOTAL]),0)"

    Range("AJ2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2023"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2023"")*TABJOUR[FREQUENTATION]),0)"

    Range("AK2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2023"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2023"")*TABJOUR[CLIENT]),0)"

    Range("AL2").Formula2R1C1 = _
        "=IF([@EXCERCICE]=""EXERCICE 2023"",SUMPRODUCT((TABJOUR[CODE_MAG]=RC1)*(TABJOUR[MOIS]*1=RC4)*(TABJOUR[EXCERCICE]=""EXERCICE 2023"")*TABJOUR[QTE TOTAL]),0)"

    'Calculate

    Range("TABMOIS[[CA_NET_TTC_TOTAL]:[QTE 2023]]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False

    Application.DisplayAlerts = True
    'Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    Range("A1").Select

    MsgBox "Durée : " & (Timer - timerDebut) & " sec."

End Sub
3bdd-test-v01.zip (1.35 Mo)
Le 08/02/2023 à 20:52

Hello,

Remplace tes sommeprod par des somme.si.ens ça devrait être + rapide

exemple pour ta première formule :

    Range("E2").Formula2R1C1 = _
        "=SUMIFS(TABJOUR!C[6],TABJOUR!C[-4],[@[CODE_MAG]],TABJOUR!C[-2],TABMOIS!RC[-2],TABJOUR!C[-1],TABMOIS!RC[-1])"
Le 10/02/2023 à 15:10

Bonjour Rag02700, Bonjour toutes et tous,

Rag02700 effectivement Somme.Si.Ens améliore grandement la vitesse d'exécution versus Sommeprod (la force de l'habitude de la syntaxe, de la versatilité et de la puissance de sommeprod me fait très souvent aborder mes problèmes avec cette fonction).Merci donc à toi pour cette première amélioration.

A ton avis et celui de vous toutes et toys voyez d'autres pistes pour améliorer enocre le temps de traitement ?

Merci

Trés cordialement

Hugues

Sub MAJTABMOIS()
'
' mettre à jour TABMOIS

Dim timerDebut As Double

timerDebut = Timer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("TABMOIS").Activate

   Range("TABMOIS[[CA_NET_TTC_TOTAL]:[QTE TOTAL 2023]]").ClearContents

    Range("E2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

    Range("F2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

    Range("G2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

    Range("H2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

    Range("I2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

    Range("L2").Formula2R1C1 = _
        "=XLOOKUP([@ANNEE]&[@MOIS],TABEXERCICE[ANNEE]&TABEXERCICE[MOIS],TABEXERCICE[EXERCICE],""ns"")"

    Range("M2").FormulaR1C1 = _
        "=IF([@MOIS]=4,1,IF([@MOIS]=5,2,IF([@MOIS]=6,3,IF([@MOIS]=7,4,IF([@MOIS]=8,5,IF([@MOIS]=9,6,IF([@MOIS]=10,7,IF([@MOIS]=11,8,IF([@MOIS]=12,9,IF([@MOIS]=1,10,IF([@MOIS]=2,11,IF([@MOIS]=3,12))))))))))))"

    Range("N2").FormulaR1C1 = "=TEXT([@MOIS]*29,""mmmm"")"

    Range("O2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("P2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("Q2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("R2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("S2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("T2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

    Range("U2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

    Range("V2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

    Range("W2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

    Range("X2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

    Range("Y2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

    Range("Z2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

    Range("AA2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

    Range("AB2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

    Range("AC2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

    Range("AD2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

    Range("AE2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

    Range("AF2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

    Range("AG2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

    Range("AH2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

    Range("AI2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

    Range("AJ2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

    Range("AJ3").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

    Range("AK2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

    Range("AL2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

    'Calculate

    Range("TABMOIS[[CA_NET_TTC_TOTAL]:[QTE TOTAL 2023]]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False

    Application.DisplayAlerts = True
    'Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    Range("A1").Select

    MsgBox "Durée : " & (Timer - timerDebut) & " sec."

End Sub
9bdd-test-v02.zip (1.33 Mo)
Le 10/02/2023 à 17:43

Hello,

Ce sont les indirect qui bouffent du temps maintenant.

Si tu veux garder la même façon de procéder, je te conseille de mettre tes colonnes de calcul sous forme de texte dans des variables.

Comme ceci :

    Tabj_CA_Net = "TABJOUR[CA_NET_TTC_TOTAL]"
    Tabj_Mag = "TABJOUR[CODE_MAG]"
    Tabj_Annee = "TABJOUR[ANNEE]"
    Tabj_Mois = "TABJOUR[MOIS]"

    Range("E2").Formula = "=SUMIFS(" & Tabj_CA_Net & "," & Tabj_Mag & ",[@[CODE_MAG]]," & Tabj_Annee & ",[@ANNEE]," & Tabj_Mois & ",[@MOIS])"
Le 15/02/2023 à 17:38

Bonjour Rag02700, Toutes et tous,

Merci beaucoup Rag02700, car même si je n'ai pas repris la suppression des indirect (car cela m'aurait demander de créer un grand nombre de variables et que je veux finalement aussi conserver les formules en dure dans la première ligne du tableau pour ma propre compréhension et lisibilité tant de la macro que des calculs faits sur la base) tes conseils m'ont vraiment permis d'optimiser mon code. Tant sur la rapidité d'exécution de la macro que sur l'utilisation de la base qui ne contient presque plus de formules mais des valeurs issues des calculs de la macro.

Tu écris dans ton dernier post. :"Si tu veux garder la même façon de procéder", est-ce que cela peut signifier que tu aurais une autre approche globale à me proposer ?

Encore une fois, merci

Très cordialement

Hugues

Ci dessous le code que j'ai construit grâce à tes conseils, n'hésite pas me dire mis à part les indirect si tu penses que l'on peut encore optimiser.

Sub MAJTABMOISBIS()
    '
    ' mettre à jour TABMOIS

    Dim timerDebut As Double
    Dim lastRow As Long

    timerDebut = Timer

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("TABMOIS")

    With ws
        .Range("TABMOIS[[CA_NET_TTC_TOTAL]:[QTE TOTAL 2023]]").ClearContents

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Range("E2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

        .Range("F2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

        .Range("G2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

        .Range("H2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

        .Range("I2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[ANNEE2],[@ANNEE],TABJOUR[MOIS2],[@MOIS])"

        .Range("J2").Formula2R1C1 = _
        "=SUMPRODUCT(('OBJECTIFS INITIAUX'!R1C4:R1C18=[@MAGASIN])*('OBJECTIFS INITIAUX'!R3C1:R14C1=[@ANNEE])*('OBJECTIFS INITIAUX'!R3C2:R14C2=[@MOIS])*'OBJECTIFS INITIAUX'!R3C4:R14C18)"

        .Range("K2").FormulaR1C1 = _
        "=XLOOKUP([@[CODE_MAG]],TABCOMP[CODE MAG],TABCOMP[TYPE],""ns"")"

        .Range("L2").Formula2R1C1 = _
        "=XLOOKUP([@ANNEE]&[@MOIS],TABEXERCICE[ANNEE]&TABEXERCICE[MOIS],TABEXERCICE[EXERCICE],""ns"")"

        .Range("M2").FormulaR1C1 = _
        "=IF([@MOIS]=4,1,IF([@MOIS]=5,2,IF([@MOIS]=6,3,IF([@MOIS]=7,4,IF([@MOIS]=8,5,IF([@MOIS]=9,6,IF([@MOIS]=10,7,IF([@MOIS]=11,8,IF([@MOIS]=12,9,IF([@MOIS]=1,10,IF([@MOIS]=2,11,IF([@MOIS]=3,12))))))))))))"

        .Range("N2").FormulaR1C1 = "=TEXT([@MOIS]*29,""mmmm"")"

        .Range("O2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("P2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("Q2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("R2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("S2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("T2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&R1C5&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C,LEN(R1C)-3))"

        .Range("U2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

        .Range("V2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

        .Range("W2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C15,LEN(R1C15)-3))"

        .Range("X2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

        .Range("Y2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

        .Range("Z2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C16,LEN(R1C16)-3))"

        .Range("AA2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

        .Range("AB2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

        .Range("AC2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C17,LEN(R1C17)-3))"

        .Range("AD2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

        .Range("AE2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

        .Range("AF2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C18,LEN(R1C18)-3))"

        .Range("AG2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

        .Range("AH2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

        .Range("AI2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C19,LEN(R1C19)-3))"

        .Range("AJ2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

        .Range("AJ3").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

        .Range("AK2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

        .Range("AL2").FormulaR1C1 = _
        "=SUMIFS(INDIRECT(""TABJOUR[""&LEFT(R1C,FIND("" "",R1C,FIND("" "",R1C)+1)-1)&""]""),TABJOUR[CODE_MAG],[@[CODE_MAG]],TABJOUR[MOIS2],[@MOIS],TABJOUR[EXERCICE],RIGHT(R1C20,LEN(R1C20)-3))"

            With .Range("E3:AL" & lastRow)
            .Value = .Value
            End With

    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Range("A1").Select

    MsgBox "Durée : " & (Timer - timerDebut) & " sec."

End Sub
Rechercher des sujets similaires à "optimiser code qui agit beaucoup trop choix defaut lignes"