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

C

CHALET53

XLDnaute Barbatruc

• 23 Mai 2014

• #2

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+

 

Vote positif 0 Vote négatif

Robert

XLDnaute Barbatruc

Repose en paix

• 23 Mai 2014

• #3

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é...

 

Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc

• 23 Mai 2014

• #4

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+😎

 

Dernière édition: 23 Mai 2014

Vote positif 0 Vote négatif

S

st007

XLDnaute Barbatruc

• 23 Mai 2014

• #5

Re : Somme d'une plage sous condition en VBA

Bonjour tous,

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

Code:

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

en ligne 3 et étirée fonctionne sans soucis

 

Dernière édition: 23 Mai 2014

Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc

• 23 Mai 2014

• #6

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+ 😎

 

Vote positif 0 Vote négatif

S

st007

XLDnaute Barbatruc

• 23 Mai 2014

• #7

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 23 Mai 2014

• #8

Re : Somme d'une plage sous condition en VBA

Bonjour à tous,

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

Code:

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+

 

Pièces jointes

• SOMME.SI VBA(1).xls

  58 KB · Affichages: 32

Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc

• 23 Mai 2014

• #9

Re : Somme d'une plage sous condition en VBA

Bonjour Maître job75

Trop Top comme toujours. Merci infiniment.


A+ 😎

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 23 Mai 2014

• #10

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 :

Code:

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

Tester avec ce fichier (2).

A+

 

Pièces jointes

• SOMME.SI VBA(2).xls

  49 KB · Affichages: 32

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 24 Mai 2014

• #11

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 :

Code:

.[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+

 

Pièces jointes

• SOMME.SI VBA(3).xls

  49 KB · Affichages: 31

Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc

• 25 Mai 2014

• #12

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+ 😎

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 25 Mai 2014

• #13

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 :

Code:

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.

 

Pièces jointes

• SOMME.SI VBA(4).xls

  56.5 KB · Affichages: 29

Dernière édition: 25 Mai 2014

Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc

• 25 Mai 2014

• #14

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

Bonsoir job75

Lone-wolf avait dit :

toujours à la recherche du parfait.

Cliquez pour agrandir...

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

Très bonne soirée 😎

 

Vote positif 0 Vote négatif