[job75 a résolu] Somme d'une plage sous condition en VBA

[Lone-wolf]

bonjour à tous 😀

je cherche à réaliser une macro pour totaliser les sommes d'une commande si celles-ci correspondent au numéro de commande.

Exemple:
Feuil1
Commande n° 1: article = X: =55.-
Commande n° 1: article = Y: =25.-
Commande n° 1: article = Z: =75.-
Commande n° 2: article = A: =25.-
Commande n° 2: article = B: =85.-

Feuil2
Commande n° 1: Total = 155.-
Commande n° 2: Total = 110.-

Voir PJ

Merci pour votre aide



A+ 😎

 

[CHALET53]

Re : Somme d'une plage sous condition en VBA

Bonjour,

En modifiant ton format colonne H de la feuille 1 (qui n'est pas vu comme un format nombre)

=SOMMEPROD((Feuil1!$B$2:$B$7=Feuil2!B3)*(Feuil1!$H$2:$H$7))

a+

 

[Robert]

Re : Somme d'une plage sous condition en VBA

Bonjour Loup Solitaire, bonjour le forum,

Ce que je ne comprends pas dans ton exemple c'est par quelle magie des données apparaissent dans l'onglet Feuil2 qui ne sont pas dans l'onglet Feuil1...?

Je crois que tu devrais faire la chose suivante :
Onglet Feuill1 les données.
Onglet Feuil2 ce que tu as avant le traitement de la macro.
Onglet Feuil3 ce que tu voudrais dans l'onglet Feuil2, après le traitement de la macro.
Ça serait plus clair. En tous cas pour moi...

[Édition]
Bonjour Chalet on s'est croisé...

 

[Lone-wolf]

Re : Somme d'une plage sous condition en VBA

Bonjour CHALET53, Robert

Merci tout d'abord d'avoir répondu.

@Robert: aaaaahh! C'est la magie ACCESS. 😉 Et oui, je reprends sous Excel un travail effectué sous Access et qui donne ces résultat suite à une requête.

@CHALET53, merci pour ta proposition, mais si je pouvais l'avoir en VBA ça serait sympa.
EDIT: la formule affiche erreur de valeur #VALEUR.


A+😎

 

[st007]

Re : Somme d'une plage sous condition en VBA

Bonjour tous,

#valeur à cause de la ???? en Feuil1!H2

=SOMMEPROD((Feuil1!$B$3:$B$7=Feuil2!B3)*Feuil1!$H$3:$H$7)

en ligne 3 et étirée fonctionne sans soucis

 

[Lone-wolf]

Re : Somme d'une plage sous condition en VBA

Bonjour st007,

ah d'accord!. Mais comment transposer la formule en VBA avec WorksheetFunction.Sumproduct ??


A+ 😎

 

[st007]

Re : Somme d'une plage sous condition en VBA

L'enregistreur de macro donne :
Sub Macro1()
'
' Macro1 Macro
'
Range("J3:J4").Select
Selection.Formula = _
"=SUMPRODUCT((Feuil1!$B$3:$B$7=Feuil2!B3)*Feuil1!$H$3:$H$7)"
Range("J4").Select
End Sub

 

[job75]

Re : Somme d'une plage sous condition en VBA

Bonjour à tous,

Une solution VBA avec cette macro dans le code de Feuil2 :

Private Sub Worksheet_Activate()
Dim ad1$, ad2$, r1 As Range, r2 As Range, t1, t2, i&
With Feuil1 'CodeName
  .[H:H].Replace ".-", "", xlPart 'nettoyage...
  .[H:H].Replace ".", "." 'remplace le point par le point...
  ad1 = .[B:B].Address(, , , True)
  ad2 = .[H:H].Address(, , , True)
End With
Set r1 = Range("B1", Range("B" & Rows.Count).End(xlUp)(2)) 'au moins 2 éléments
Set r2 = r1.Offset(, 6) 'colonne H
t1 = r1: t2 = r2 'matrices, plus rapide
For i = 2 To UBound(t1) - 1
  t2(i, 1) = Evaluate("SUMIF(" & ad1 & "," & t1(i, 1) & "," & ad2 & ")")
Next
r2 = t2
End Sub

Fichier joint.

A+

 

[Lone-wolf]

Re : Somme d'une plage sous condition en VBA

Bonjour Maître job75

Trop Top comme toujours. Merci infiniment.


A+ 😎

 

[job75]

Re : [job75 a résolu] Somme d'une plage sous condition en VBA

Re,

Le tableau en Feuil2 pouvant être raccourci, il peut être utile d'ajouter à la fin de la macro :

Range(r2(UBound(t2)), r2(Rows.Count)).ClearContents

Tester avec ce fichier (2).

A+

 

[job75]

Re : [job75 a résolu] Somme d'une plage sous condition en VBA

Bonjour le fil, le forum,

Noter que sur les fichiers précédents l'instruction :

.[H:H].Replace ".", "." 'remplace le point par le point...

n'est pas nécessaire.

Elle l'est si en colonne H de Feuil1 il y a des valeurs textes comme 35.20.

Fichier (3).

A+

 

[Lone-wolf]

Re : [job75 a résolu] Somme d'une plage sous condition en VBA

Bonsoir Maître,

toujours à la recherche du parfait. 1 million de fois Merci, merci pour tout ce que vous faites.


A+ 😎

 

[job75]

Re : [job75 a résolu] Somme d'une plage sous condition en VBA

Bonsoir Lone-wolf,

Merci beaucoup, en fait j'ai une meilleure solution.

Elle consiste à entrer une seule formule dans la plage en colonne H :

Private Sub Worksheet_Activate()
Dim ad1$, ad2$, r1 As Range, r2 As Range
With Feuil1 'CodeName
  .[H:H].Replace ".-", "", xlPart 'nettoyage...
  .[H:H].Replace ".", "." 'remplace le point par le point...
  ad1 = .[B:B].Address(, , , True)
  ad2 = .[H:H].Address(, , , True)
End With
Set r1 = Range("B2", Range("B" & Rows.Count).End(xlUp)(2))
Set r2 = r1.Offset(, 6) 'colonne H
r2.Formula = "=SUMIF(" & ad1 & "," & r1.Address & "," & ad2 & ")"
r2 = r2.Value 'ne garde que les valeurs
Range(r2(r2.Rows.Count), r2(Rows.Count - 1)).ClearContents
End Sub

Fichier (4).

J'ai testé sur Excel 2010 les fichiers (3) et (4) avec 6000 lignes sur les 2 feuilles :

- fichier (3) => 4,53 secondes

- fichier (4) => 1,75 secondes.

Bonne fin de soirée.

 

[Lone-wolf]

Re : [job75 a résolu] Somme d'une plage sous condition en VBA

Bonsoir job75

Lone-wolf avait dit :

toujours à la recherche du parfait.

quelque chose me dit.... que ce n'est pas encore fini; n'est-ce pas job? 😉

Très bonne soirée 😎