Somme si ens en VBA

Le 07/12/2022 à 06:37

Bonjour,

on peut bloquer les calculs le temps que toutes les formules soient écrites puis lancer le calcul à la fin, mais au final je pense que se sera pareil.

ajout de:

Application.Calculation = xlCalculationManual

et de:

Application.Calculation = xlCalculationAutomatic

le code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 9 Then
        On Error GoTo Sortie
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        DerLig = Sheets("Forecasts").Range("A" & Rows.Count).End(xlUp).Row
        For i = 11 To DerLig
            Select Case Cells(i, "H")
                Case Is = "LY Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Fd de rayon"")"
                Case Is = "LY Promo invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!F:F;""Commande Promotionnelle"")"
                Case Is = "LY Total Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ")"
                Case Is = "Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Fd de rayon"")"
                Case Is = "Promo invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!F:F;""Commande Promotionnelle"")"
                Case Is = "Total Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ")"
            End Select
        Next i
        Range("BQ11:BQ" & DerLig).FormulaR1C1 = "=SUM(RC[-60]:RC[-49])"
        Range("BR11:BR" & DerLig).FormulaR1C1 = "=SUM(RC[-61]:RC[-53])"
        Range("BS11:BS" & DerLig).FormulaR1C1 = "=SUM(RC[-53]:RC[-51])"
        Range("BT11:BT" & DerLig).FormulaR1C1 = "=SUM(RC[-51]:RC[-40])"
        Range("BU11:BU" & DerLig).FormulaR1C1 = "=SUM(RC[-52]:RC[-44])"
        Range("BV11:BV" & DerLig).FormulaR1C1 = "=SUM(RC[-44]:RC[-42])"
        Range("BW11:BW" & DerLig).FormulaR1C1 = "=SUM(RC[-42]:RC[-31])"
        Range("BX11:BX" & DerLig).FormulaR1C1 = "=SUM(RC[-43]:RC[-35])"
        Range("BY11:BY" & DerLig).FormulaR1C1 = "=SUM(RC[-35]:RC[-33])"
        Range("BZ11:BZ" & DerLig).FormulaR1C1 = "=SUM(RC[-33]:RC[-22])"
        Range("CA11:CA" & DerLig).FormulaR1C1 = "=SUM(RC[-34]:RC[-26])"
        Range("CB11:CB" & DerLig).FormulaR1C1 = "=SUM(RC[-26]:RC[-24])"
        Range("CC11:CC" & DerLig).FormulaR1C1 = "=SUM(RC[-24]:RC[-13])"
        Range("CD11:CD" & DerLig).FormulaR1C1 = "=SUM(RC[-25]:RC[-17])"
        Range("CE11:CE" & DerLig).FormulaR1C1 = "=SUM(RC[-17]:RC[-15])"
        Application.Calculation = xlCalculationAutomatic
        'Range("I11:CE" & DerLig).Value = Range("I11:CE" & DerLig).Value 'Enlevez l'apostrophe devant la ligne de code pour ne conserver que les valeurs tout en écrasant les formules
Sortie:
        Application.EnableEvents = True
    End If
End Sub

Mais le problème ne vient que de là, je viens de voir que les colonnes A,C,D,E,F sont remplies de formules avec des "INDEX " et c'est celles-là qu'il faut aussi intégrer dans le code. Il n'y a que vous qui puissiez le faire puisqu'elles font références à vos dossiers. Utilisez l'enregistreur de macro pour chaque formule, placez-vous sur la ligne 5 et sur chaque colonne comportant une formule et validez chaque formule dans la "barre de formule" ensuite, récupérez ces lignes de codes et modifiez-les pour les coller dans le précédent code avant la ligne "Application.Calculation = xlCalculationAutomatic " sous cette forme:

Exemple pour la colonne A: Range("A5:A" & DerLig)=et la formule récupérée avec l'enregistreur

Faire la même chose pour les autres colonnes.

Cdlt

Le 20/12/2022 à 14:37

Bonjour,

je me permets de vous solliciter à nouveau afin de savoir si possible désactiver le copier coller à partir de la colonne BQ car il y a des erreurs sur les et à chaque fois ça recolle en dure sur la base des erreurs.

En gros ne laisser la macro pour les somme.si.en que jusque au dernier mois soit Décembre 2023 soit la colonne BP

Quelle serait la manip dans le code pour rajouter une colonne après BP?

D'avance merci de votre aide.

max

Le 20/12/2022 à 14:57

Bonjour,

Il suffit de mettre une apostrophe au début de chaque ligne de code ci-dessousi:

        Range("BQ11:BQ" & DerLig).FormulaR1C1 = "=SUM(RC[-60]:RC[-49])"
        Range("BR11:BR" & DerLig).FormulaR1C1 = "=SUM(RC[-61]:RC[-53])"
        Range("BS11:BS" & DerLig).FormulaR1C1 = "=SUM(RC[-53]:RC[-51])"
        Range("BT11:BT" & DerLig).FormulaR1C1 = "=SUM(RC[-51]:RC[-40])"
        Range("BU11:BU" & DerLig).FormulaR1C1 = "=SUM(RC[-52]:RC[-44])"
        Range("BV11:BV" & DerLig).FormulaR1C1 = "=SUM(RC[-44]:RC[-42])"
        Range("BW11:BW" & DerLig).FormulaR1C1 = "=SUM(RC[-42]:RC[-31])"
        Range("BX11:BX" & DerLig).FormulaR1C1 = "=SUM(RC[-43]:RC[-35])"
        Range("BY11:BY" & DerLig).FormulaR1C1 = "=SUM(RC[-35]:RC[-33])"
        Range("BZ11:BZ" & DerLig).FormulaR1C1 = "=SUM(RC[-33]:RC[-22])"
        Range("CA11:CA" & DerLig).FormulaR1C1 = "=SUM(RC[-34]:RC[-26])"
        Range("CB11:CB" & DerLig).FormulaR1C1 = "=SUM(RC[-26]:RC[-24])"
        Range("CC11:CC" & DerLig).FormulaR1C1 = "=SUM(RC[-24]:RC[-13])"
        Range("CD11:CD" & DerLig).FormulaR1C1 = "=SUM(RC[-25]:RC[-17])"
        Range("CE11:CE" & DerLig).FormulaR1C1 = "=SUM(RC[-17]:RC[-15])"

ainsi les lignes passeront en remarque (de couleur verte) et elles ne seront plus lues lors de l'exécution du code.

Cdlt

Le 20/12/2022 à 15:23

un grand merci à vous

Le 21/12/2022 à 17:44

Bonjour,

J4AI rajouté ce bout de code afin d'avoir une nouvelle Somme.Si.Ens qui irait sourcer dans une autre feuille nommée "Data Promos":

Case Is = "VRAC PROMO"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data Promos!$F:$F;Data Promos!$A:$A;$A" & i & ";Data Promos!$B:$B;$C" & i & ";Data Promos!$K:$K;I$4;Data Promos!$J:$J;$B" & i & ";Data Promos!$I:$I;$G" & i & ";Data!$D:$D;""VRAC PROMO"")"

j'ai écrit le code comme ci-dessous:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 9 Then
        On Error GoTo Sortie
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        DerLig = Sheets("Forecasts").Range("A" & Rows.Count).End(xlUp).Row
        For i = 11 To DerLig
            Select Case Cells(i, "H")
                Case Is = "LY Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Fd de rayon"")"
                Case Is = "LY Promo invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Commande Promotionnelle"")"
                Case Is = "LY Total Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$2;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ")"
                Case Is = "Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Fd de rayon"")"
                Case Is = "Promo invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ";Data!$F:$F;""Commande Promotionnelle"")"
                Case Is = "Total Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A" & i & ";Data!$D:$D;$C" & i & ";Data!$K:$K;I$4;Data!$E:$E;$B" & i & ";Data!$G:$G;$G" & i & ")"

Case Is = "VRAC PROMO"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data Promos!$F:$F;Data Promos!$A:$A;$A" & i & ";Data Promos!$B:$B;$C" & i & ";Data Promos!$K:$K;I$4;Data Promos!$J:$J;$B" & i & ";Data Promos!$I:$I;$G" & i & ";Data!$D:$D;""VRAC PROMO"")"

            End Select
        Next i
        'Range("BQ11:BQ" & DerLig).FormulaR1C1 = "=SUM(RC[-60]:RC[-49])"
        'Range("BR11:BR" & DerLig).FormulaR1C1 = "=SUM(RC[-61]:RC[-53])"
        'Range("BS11:BS" & DerLig).FormulaR1C1 = "=SUM(RC[-53]:RC[-51])"
        'Range("BT11:BT" & DerLig).FormulaR1C1 = "=SUM(RC[-51]:RC[-40])"
        'Range("BU11:BU" & DerLig).FormulaR1C1 = "=SUM(RC[-52]:RC[-44])"
        'Range("BV11:BV" & DerLig).FormulaR1C1 = "=SUM(RC[-44]:RC[-42])"
        'Range("BW11:BW" & DerLig).FormulaR1C1 = "=SUM(RC[-42]:RC[-31])"
        'Range("BX11:BX" & DerLig).FormulaR1C1 = "=SUM(RC[-43]:RC[-35])"
        'Range("BY11:BY" & DerLig).FormulaR1C1 = "=SUM(RC[-35]:RC[-33])"
        'Range("BZ11:BZ" & DerLig).FormulaR1C1 = "=SUM(RC[-33]:RC[-22])"
        'Range("CA11:CA" & DerLig).FormulaR1C1 = "=SUM(RC[-34]:RC[-26])"
        'Range("CB11:CB" & DerLig).FormulaR1C1 = "=SUM(RC[-26]:RC[-24])"
        'Range("CC11:CC" & DerLig).FormulaR1C1 = "=SUM(RC[-24]:RC[-13])"
        'Range("CD11:CD" & DerLig).FormulaR1C1 = "=SUM(RC[-25]:RC[-17])"
        'Range("CE11:CE" & DerLig).FormulaR1C1 = "=SUM(RC[-17]:RC[-15])"
        Application.Calculation = xlCalculationAutomatic
        Range("I11:BP" & DerLig).Value = Range("I11:BP" & DerLig).Value 'Enlevez l'apostrophe devant la ligne de code pour ne conserver que les valeurs tout en écrasant les formules
Sortie:
        Application.EnableEvents = True
    End If
End Sub

Ma question est de savoir si j'ai bien fait de mettre ce bout de code au dessous, ou s'il fallait refaire tout le code pour cette nouvelle feuille "Data Promos"

D'avance merci de votre aide!

Max

Le 21/12/2022 à 18:55

Bonsoir,

En principe, c'est bon puisque la source "Data Promos", bien que différente des autres conditions, est spécifiée, je ne peux pas le vérifier, mais de votre côté, je suppose que vous avez testé et que cela fonctionne.

Si tout marche bien, passez le sujet en "Résolu".

Cdlt

Le 22/12/2022 à 10:10

Bonjour,

je vous mets un bout de fichier avec le code, car ça n'a pas l'aide de fonctionner.

SI vous pouviez me dire d'ou vient le problème.

D'avance merci de votre aide.

Max

5test-8.xlsx (109.38 Ko)

Rechercher des sujets similaires à "somme ens vba"