Somme si ens en VBA

Le 02/12/2022 à 15:27

bonjour à tous,

je cherche un code VBA pour remplacer les formules en colonne B de la feuille "recap" avec mise à jour à l'activation de la feuille.

j'ai parcouru quelques fils sur le forum mais pas de solution de ce type.

merci d'avance.

Max

16test-8.xlsx (103.65 Ko)
Le 02/12/2022 à 15:29

A partir de la cellule I11 pardon;

Le 02/12/2022 à 17:45

Bonsoir,

Essayez ceci, la feuille est recalculée à chaque changement de valeur dans les colonnes de A à H.

9didi654613-somme-si-ens-en-vba.xlsm (111.73 Ko)

Cdlt

Le 02/12/2022 à 17:54

C'est un très bon début, sachant que j'aurai d'autres lignes à calculer, je peux vous envoyer un nouveau fichier?

Le 02/12/2022 à 18:03
j'aurai d'autres lignes à calculer, je peux vous envoyer un nouveau fichier?
Normalement le nombre de lignes importe peu, puisque le code s'adaptera en fonction de ce nombre de ligne
Le 02/12/2022 à 18:21

mais avec des critères de recherche différents

Exemple:

LY Promo invoiced: est égale a ce qui contient "Commande Promotionnelle" dans le colonne F (drivers)

Le 02/12/2022 à 18:30
LY Promo invoiced: est égale a ce qui contient "Commande Promotionnelle" dans le colonne F (drivers)
Comprend pas à quoi cela correspond!
il faut un fichier avec les cas de figure bien commentés
Le 05/12/2022 à 10:00

Bonjour,

Tout d'abord désolé de la réponse tardive.

ci-joint le fichier avec les epxlications :)

3test-8.xlsx (111.67 Ko)

D'avance merci

Max

Le 05/12/2022 à 10:50

Bonjour,

Tout changement de valeurs dans les colonnes A à H et à partir de la ligne 11 réécrit les formules

4didi654613-somme-si-ens-en-vba-2.xlsm (125.10 Ko)

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
        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;$A11;Data!$D:$D;$C11;Data!$K:$K;I$2;Data!$E:$E;$B11;Data!$G:$G;$G11;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;$A11;Data!$D:$D;$C11;Data!$K:$K;I$2;Data!$E:$E;$B11;Data!$G:$G;$G12;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;$A11;Data!$D:$D;$C11;Data!$K:$K;I$2;Data!$E:$E;$B11;Data!$G:$G;$G13)"
                Case Is = "Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A11;Data!$D:$D;$C11;Data!$K:$K;I$4;Data!$E:$E;$B11;Data!$G:$G;$G14;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;$A11;Data!$D:$D;$C11;Data!$K:$K;I$4;Data!$E:$E;$B11;Data!$G:$G;$G15;Data!F:F;""Commande Promotionnelle"")"
                Case Is = "Total Invoiced"
                    Range("I" & i & ":BP" & i).FormulaLocal = "=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A11;Data!$D:$D;$C11;Data!$K:$K;I$4;Data!$E:$E;$B11;Data!$G:$G;$G16)"
            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])"
        '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

Cdlt

Le 05/12/2022 à 11:07

Bonjour,

J'ai un peu de mal à intégrer la macro, faut-il la mettre dans un module?

Merci

Le 05/12/2022 à 11:38

Dans le module de la feuille concernée

didi654613
Le 05/12/2022 à 11:42

Bonjour,

C'est fait, un grand merci à vous.

Dernière question, possible de mettre les résultats en dure et non en valeur?

Merci

max

Le 05/12/2022 à 11:45

Dans le code transmis, vers la fin, il y a cette ligne

 '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

Donc enlevez l'apostrophe qui se trouve devant range, et vous aurez les résultats en dur, sans formule.

Le 05/12/2022 à 11:53

Un grand merci pour votre aide! Excellente journée

Le 05/12/2022 à 13:39

je voudrai juste faire une petite suggestion, au niveau de la macro, possible de modifier les critères, je m'explique le critère reste toujours sur la ligne 11, hors il doit descendre de ligne en ligne pour suivre la cadence des lignes et des formules.

Exemple en ligne 12:

=SOMME.SI.ENS(Data!$H:$H;Data!$C:$C;$A11;Data!$D:$D;$C11;Data!$K:$K;I$2;Data!$E:$E;$B11;Data!$G:$G;$G11)

tous les critères doivent se situer sur la ligne 12 et non 11.

D'avance merci de votre aide

Le 05/12/2022 à 13:52

Si j'ai bien compris:

le fichier

7didi654613-somme-si-ens-en-vba-3.xlsm (125.70 Ko)

Avec le code modifié

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 9 Then
        On Error GoTo Sortie
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        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;$G12;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;$G13)"
                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;$G14;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;$G15;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;$G16)"
            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])"
        '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
Le 05/12/2022 à 15:25

je viens de faire un test, je viens de rajouter un bloc de ligne (de la ligne 17 à 28).

en ligne 23, tout est ok, les critères de recherche sont bien sur la ligne 23, hors la ligne de dessous, la 24, le critère en colonne G va prendre celui en ligne 12.

Un moyen de rectifier cette erreur car je vais avoir plus de 3K lignes

D'avance merci

2didi654613-somme-si-ens-en-vba-3.xlsm (136.15 Ko)
Le 05/12/2022 à 16:09

Quelques valeurs étaient restées en dur dans les formules, voici le code corrigé, et dites-moi si c'est correct.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 9 Then
        On Error GoTo Sortie
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        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])"
        '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

Cdlt

Le 05/12/2022 à 16:13

Excellent, exactement le résultat attendu!

Encore merci à vous et excellente journée

Le 06/12/2022 à 18:30

Bonjour,

je me permets de vous sollciter à nouveau, non pas pour une demande de modification car le code marche à merveille, c'est juste pour vous demander si vous avez un bout de code pour améliorer le temps de réponse du calcul du fichier car dès que je rajouter des lignes, c'est super long à calculer (j'en suis à 3K lignes).

D'avance merci de votre aide!

Rechercher des sujets similaires à "somme ens vba"