Somme si ens en VBA
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
A partir de la cellule I11 pardon;
Bonsoir,
Essayez ceci, la feuille est recalculée à chaque changement de valeur dans les colonnes de A à H.
Cdlt
C'est un très bon début, sachant que 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
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)
Comprend pas à quoi cela correspond!
il faut un fichier avec les cas de figure bien commentés
Bonjour,
Tout d'abord désolé de la réponse tardive.
ci-joint le fichier avec les epxlications :)
D'avance merci
Max
Bonjour,
Tout changement de valeurs dans les colonnes A à H et à partir de la ligne 11 réécrit les formules
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
Bonjour,
J'ai un peu de mal à intégrer la macro, faut-il la mettre dans un module?
Merci
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
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.
Un grand merci pour votre aide! Excellente journée
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
Si j'ai bien compris:
le fichier
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
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
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
Excellent, exactement le résultat attendu!
Encore merci à vous et excellente journée
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!