Somme si cellule contient VBA

Le 22/01/2021 à 13:05

Bonjour,

J'essaie d'insérer la formule somme si avec VBA dont le critère en cellule C20 ne comporte par l'intégralité des caractères dans les cellules de la plage où doit s'appliquer la condition.

Exemple: En colonne B j'ai des chiffres qui vont de 112000 à 112200 et mon critère est la cellule C20 qui affiche 112. Je souhaite faire apparaitre le résultat dans la cellule G20. Si je veux que ça marche je suis obligé d'indiquer par exemple 112002 comme ceci en cellule C20 :

Sub somme()

Sheets("MEF").Cells(20, 8).Value = Application.WorksheetFunction.SumIf(Sheets("MEF").Range("B:B"),"112002",Sheets("MEF").Range("G:G"))

End Sub

J'ai essayé cela mais ça ne fonctionne pas:

Sub somme()

Sheets("Débiteurs_MEF").Cells(20, 8).Value =

Application.WorksheetFunction.SumIf(Sheets("MEF").Range("B:B"), C20&"*",Sheets("MEF").Range("G:G"))

End Sub

Avez-vous une idée ?

Merci,

Le 22/01/2021 à 13:20

Bonjour

Un exemple de ton fichier serait le bienvenu avec explications bien sur

Crdlt

Le 22/01/2021 à 13:28

Bonjour ML88, Joco7915, le forum,

Un essai...de ce que j'ai compris....

  • Si en colonne B, les 3 premiers caractères correspondent à la valeur de la cellule C20, on additionne les valeurs de la colonne G.
  • On inscrit le résultat en H20.
Sub somme()
 Dim compteur

  compteur = 0

  Application.ScreenUpdating = False

  With Sheets("MEF")
   For i = 1 To .Range("B" & Rows.Count).End(xlUp).Row
    If Left(.Range("B" & i), 3) Like .Range("C20") Then compteur = compteur + .Range("G" & i)
   Next i
    .Range("H20") = compteur
  End With
End Sub
13ml88-somme.xlsm (19.37 Ko)

Cordialement,

Le 22/01/2021 à 13:30

Bonjour à tous,

Un essai en attendant le fichier, et en utilisant les balises </> pour poster du code :

Sub somme()

with Sheets("Débiteurs_MEF")
    .Cells(20, 8).Value = Application.SumIf(Sheets("MEF").Range("B:B"), .cells(20, 3).value & "*",Sheets("MEF").Range("G:G"))
end with

End Sub

Ici, je suppose que le C20 en question appartient à la feuille Débiteurs.

Cdlt,

Edit : Salut xorsankukai !

Le 22/01/2021 à 14:13
11test-macro.xlsx (9.44 Ko)

Merci pour vos réponses

Le 22/01/2021 à 14:42

Alors, c'est embêtant parce que SOMMEPROD est plus souple que SOMME.SI.ENS mais n'évite pas les caractères alphanumériques, bloquant pour les sommes tandis que SOMME.SI.ENS est plus flexible à ce niveau mais moins au niveau des critères...

Voici une solution pour la valeur de H20 dans le code :

.[H20].Value = Evaluate("SUM( IFERROR( (LEFT(B:B,3)*1=C20)* G:G, 0))")

Il s'agit d'une évaluation d'un calcul matriciel, lequel prend un peu de temps.

Sinon, une proposition de code en insérant des formules matricielles :

Sub somme()

With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).Value
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 8).FormulaArray = formule
            '.[H20].Value = Evaluate("SUM( IFERROR( (LEFT(B:B,3)*1=C20)* G:G, 0))")
        End If
    Next i
End With

End Sub

A chaque ligne "Sous-Total".

Je trouve que c'est mieux, on garde le résultat, on ne prend pas la colonne entière.

2test-macro.xlsm (15.47 Ko)

Cdlt,

Le 22/01/2021 à 15:12

Re,

Salut 3GB,

Bien joué pour la matricielle ,

Tu as oublié les lignes "Total", ............... je suppose qu'il faut cumuler les sous-totaux...(oui, j'y tiens à mon compteur...)

Petit rajout:

Sub somme()
 With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    compteur = 0
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).Value
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 9).FormulaArray = formule: .Cells(i, 9).NumberFormat = "$#,##0.00;-$#,##0.00"
            compteur = compteur + .Cells(i, 9).Value
            .Cells(i + 1, 9) = compteur: .Cells(i + 1, 9).NumberFormat = "$#,##0.00;-$#,##0.00"
        End If
    Next i
 End With
End Sub

(CTRL + E pour lancer la macro...)

4test-macro.xlsm (16.92 Ko)

Mais je suppose que tu as une autre formule sous le coude ....

Cordialement,

Le 22/01/2021 à 15:23

Merci xorsankukai, j'étais vraiment focalisé sur les sous-totaux pour l'instant, ne mettons pas la charrue avant les boeufs ! .

Oui, je vois ça, parfaite utilisation de la légendaire variable compteur !

Bien joué !

Juste au cas où, j'ai fait une petite bourde en laissant 9 à la place de 8 dans le code. Je l'ai corrigée sur le code que j'ai posté mais pas sur le fichier...

Et pour la formule, je pense qu'on aurait pu faire la somme de la colonne G 1 à i à chaque "Total" en colonne B. Mais ça m'embêterait pour le compteur

Cdlt,

Le 22/01/2021 à 15:35

J'avais pensé aussi à chaque ligne sous-total mais je ne savais pas comment l'appréhender car je débute.

Du coup merci beaucoup je vais tester ça :D

Merci à tous ;)

Le 22/01/2021 à 15:46

Re,

Et pour la formule, je pense qu'on aurait pu faire la somme de la colonne G 1 à i à chaque "Total" en colonne B. Mais ça m'embêterait pour le compteur

Mais je t'en prie, je suis prêt à sacrifier mon compteur pour ta formule, 

Sub somme()
 With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).Value
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 9).FormulaArray = formule: .Cells(i, 9).NumberFormat = "$#,##0.00;-$#,##0.00"
            .Cells(i + 1, 9) = Application.WorksheetFunction.Sum(.Range("G1:G" & i)): .Cells(i + 1, 9).NumberFormat = "$#,##0.00;-$#,##0.00"
        End If
    Next i
 End With
End Sub
13test-macro-v2.xlsm (17.64 Ko)

Amitiés,

Le 22/01/2021 à 15:58

Merci pour ton sens du sacrifice !

J'adapte juste ton code pour y inscrire la formule (et remettre la colonne 8 à la place de 9), au cas où ça aurait une importance pour ML88 :

Sub somme()
 With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).Value
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 8).FormulaArray = formule: .Cells(i, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
            .Cells(i + 1, 8).formula = "=SUM(G1:G" & i & ")": .Cells(i + 1, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
        End If
    Next i
 End With
End Sub
Le 26/01/2021 à 12:06

Bonjour,

Alors j'ai apporté quelques petites modifs pour l'adapter à ma macro déjà existante. J'ai modifié légèrement la macro initiale ce qui modifie un peu la structure du fichier est facilite la somme je trouve. Mais j'ai oublié de préciser que la racine peut être soit de 2 chiffres soit de 3 chiffres. Faudrait-il modifier "formule" dans la macro avec un "IF" et "OR" ou modifier la macro d'une manière différente ?

Exemple : Ma racine en Cells(j,3) peut être de 112 ou 13 (car les référence sont soit composé de 5 chiffres soit 6 chiffres)

Ci-joint les petites modifs

Sub somme()  

With Sheets("MEF")  

 dl = .Cells(.Rows.Count, 2).End(xlUp).Row  

     For j = 1 To dl  

       If .Cells(j, 2) = "Racine" Then  

       racine = .Cells(j, 3).Value  

4test-macro.xlsx (9.44 Ko)

       formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* G1:G" & dl & ", 0))-SUM( IFERROR( (LEFT(B1:B" & dl & ",3)*1=" & racine & ")* H1:H" & dl & ", 0))"  

       .Cells(j + 1, 9).FormulaArray = formule: .Cells(j + 1, 9).NumberFormat = "$#,##0.00;-$#,##0.00"  

 End If  

 Next j  

End With  

End Sub

Le 26/01/2021 à 12:19

Bonjour,

Voici un essai en variabilisant le longueur de la racine de compte :

Sub somme()
 With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).Value
            longueur = len(racine)
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & "," & longueur & ")*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 8).FormulaArray = formule: .Cells(i, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
            .Cells(i + 1, 8).formula = "=SUM(G1:G" & i & ")": .Cells(i + 1, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
        End If
    Next i
 End With
End Sub

ou directement dans la formule :

Sub somme()
 With Sheets("MEF")
    dl = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 1 To dl
        If .Cells(i, 2) = "Sous-Total" Then
            racine = .Cells(i, 3).address(0, 0)
            formule = "=SUM( IFERROR( (LEFT(B1:B" & dl & ",LEN(" & racine & ")*1=" & racine & ")* G1:G" & dl & ", 0))"
            .Cells(i, 8).FormulaArray = formule: .Cells(i, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
            .Cells(i + 1, 8).formula = "=SUM(G1:G" & i & ")": .Cells(i + 1, 8).NumberFormat = "$#,##0.00;-$#,##0.00"
        End If
    Next i
 End With
End Sub

Cdlt,

Le 26/01/2021 à 13:41

En variabilisant c'est top merci beaucoup je vais gagner des heures et des heures.

Par contre j'ai une erreur 1004 qui apparait vous savez comment je peux la corriger ? (VBA et moi c'est une histoire qui débute :) )

Merci encore

Le 26/01/2021 à 13:50

Une erreur sur quelle ligne ? Cette erreur ne se produit pas tout le temps ? Ou s'est-elle juste produite depuis la dernière modif ?

Sachant que je vous recommande d'utiliser le second code posté...

Le 26/01/2021 à 13:59

C'est bon je n'ai rien dit merci :) ça fonctionne

Rechercher des sujets similaires à "somme contient vba"