Traduire SOMMEPROD en VBA

[nadir****]

Bonjour,
Je voudrais pouvoir écrire en VBA l'équivalent de la fonction

=SOMMEPROD((B1:B9=C1:C9)*1)

Ceci permet de compter le nombre de cellules identiques entre deux plages.(merci dugenou)
J'ai donc tenté le code VBA suivant:

diff = WorksheetFunction.SumProduct((Range("B1:B9") = Range("C1:C9")) * 1)

Sans succès hélas.
J'ai mis un fichier exemple en pièce jointe.
Merci pour votre aide.
Nadir.

 

[JCGL]

Re : Traduire SOMMEPROD en VBA

Bonjour à tous,

Peux-tu essayer :

Range("C10").Formula = "=SUMPRODUCT((R[-9]C[-1]:R[-1]C[-1]=R[-9]C:R[-1]C)*1)"

A+ à tous

 

[nadir****]

Re : Traduire SOMMEPROD en VBA

Oui, je comprends cette réponse et merci.
J'ai simplifié peut-être un peu trop mon exemple pour être clair.

Je voudrais travailler avec des plages de dimensions différentes.
Mon code deviendrait alors

diff = WorksheetFunction.SumProduct((Plage1 =Plage2) * 1)

Dans cet exemple simplifié,
Plage1 est Range("B1:B9") et Plage2 est Range("C1:C9")

Nadir.

 

[JCGL]

Re : Traduire SOMMEPROD en VBA

Bonjour à tous,

Je voudrais travailler avec des plages de dimensions différentes

SOMMEPROD() ne supporte pas les plages de dimensions différentes.

A+ à tous

 

[JCGL]

Re : Traduire SOMMEPROD en VBA

Bonjour à tous,

Peux-tu essayer :

ActiveWorkbook.Names.Add Name:="Plage1", RefersToR1C1:="=Feuil1!R1C2:R9C2"
ActiveWorkbook.Names.Add Name:="Plage2", RefersToR1C1:="=Feuil1!R1C3:R9C3"
Range("C10").Formula = "=SUMPRODUCT((Plage1=Plage2)*1)"

A+ à tous

 

[nadir****]

Re : Traduire SOMMEPROD en VBA

Merci JCLG.
Oui bien sûr, les plages de la fonction SOMMEPROD doivent être de même dimension.
Les solutions que tu proposes sont des "retranscriptions" de la fonction SOMMEPROD.
C'est comme si on "écrivait" la fonction SOMMEPROD dans une cellule avec tous ses arguments. On peut éventuellement même l'évaluer avec EVALUATE. Mais ce n'est pas ce que je voudrais.
On perd la souplesse et la simplicité de la fonction SumProduct.
Mais peut-être que worksheetfunction.SumProduct n'offre pas les mêmes possibilités que SOMMEPROD.
Merci en tout cas pour ces réponses.
Nadir.

 

[repcheks]

Re : Traduire SOMMEPROD en VBA

Hello,

A mon avis il vaut mieux essayer de transcrire le fonctionnement de la formule, plutot que d'essayer de réécrire celle-ci. Par exemple:

Sub SumProductSimul()

    Dim Plage1 As Range, Plage2 As Range
    Dim Cel1 As Range, Cel2 As Range
    Dim i As Long
    
    Set Plage1 = Range("B1:B9")
    Set Plage2 = Range("C1:C9")
    i = 0
    
    For Each Cel1 In Plage1
    
        For Each Cel2 In Plage2
        
            If Cel2 = Cel1 Then i = i + 1
        
        Next Cel2
    
    Next Cel1
    
    MsgBox i

End Sub

Ensuite il faut bien prendre en compte les comportements qui peuvent differer: par exemple ici la fonction est sensible a la casse. si on ne veut pas qu'elle le soit on peut remplacer

If Cel2 = Cel1 Then i = i + 1

par

If UCase(Cel2) = UCase(Cel1) Then i = i + 1

ou meme

If Trim(UCase(Cel2)) = Trim(UCase(Cel1)) Then i = i + 1

pour eviter tout probleme du a un espace indu. De cette facon, 'A'='a' et 'A'='a '

Ensuite il faut voir le comportement souhaité avec les occurences multiples.

Ici, elles incrementent le resultat, alors que dans SommeProd elles ne le feraient pas, seules les occurences uniques sont comptées.

Si on veut le meme comportement, on peut opter pour quelque chose du genre:

Option Base 1

Sub SumProductSimul()

    Dim Plage1 As Range, Plage2 As Range
    Dim Cel1 As Range, Cel2 As Range
    Dim i As Long, j As Long, z As Long
    Dim aVerif() As String, bVerif As Boolean
    
    Set Plage1 = Range("B1:B9")
    Set Plage2 = Range("C1:C9")
    i = 0
    j = 0
    
    For Each Cel1 In Plage1
    
        For Each Cel2 In Plage2
        
            If Cel2 = Cel1 Then
            
                i = i + 1
                ReDim Preserve aVerif(i)
            
                If i = 0 Then
                
                    aVerif(i) = Cel2
                    j = j + 1
                
                Else
                    
                    bVerif = False
                    
                    For z = 1 To UBound(aVerif)
                    
                        If aVerif(z) = Cel1 Then bVerif = True
                    
                    Next z
                    
                    If bVerif = False Then
                    
                        j = j + 1
                        aVerif(i) = Cel2
                        
                    End If
                    
                End If
                
            End If
        
        Next Cel2
    
    Next Cel1
                    
    MsgBox j

End Sub

 

[david84]

Re : Traduire SOMMEPROD en VBA

Bonsoir,

Les solutions que tu proposes sont des "retranscriptions" de la fonction SOMMEPROD.
C'est comme si on "écrivait" la fonction SOMMEPROD dans une cellule avec tous ses arguments. On peut éventuellement même l'évaluer avec EVALUATE. Mais ce n'est pas ce que je voudrais.
On perd la souplesse et la simplicité de la fonction SumProduct.
Mais peut-être que worksheetfunction.SumProduct n'offre pas les mêmes possibilités que SOMMEPROD.

Au risque de dire une bêtise, je pense que si tu veux utiliser Sommeprod tel quel à savoir sous forme de calcul matriciel de plages, tu dois au contraire utiliser sommeprod, soit comme te l'indique Jean-Claude, soit en utilisant Evaluate sous la forme avec les crochets ou la forme longue :

Sub Test()
Dim Plage1 As Range, Plage2 As Range, Resultat&
Set Plage1 = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
Set Plage2 = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
Resultat = Evaluate("SUM((" & Plage1.Address & "=" & Plage2.Address & ")*1)")
MsgBox Resultat
End Sub

"La fonction Evaluate et son équivalent [] permettent, dans une certaine mesure, de combler une lacune importante de VBA, l'impossibilité apparente d'utiliser des formules matricielles à l'intérieur d'une macro. En effet, l'expression transmise à Evaluate est traitée implicitement par Excel comme une formule matricielle. Si l'expression effectue des opérations entre plusieurs plages multi-cellules, le résultat sera un tableau de valeurs équivalent à celui obtenu si la même expression avait été saisie par Ctrl-Maj-Entrée dans une plage de la feuille de calcul active."

Ce lien n'existe plus

Si tu veux passer par l'utilisation des worksheetFunction, je ne pense pas que tu puisses travailler de la sorte sur des plages. Il te faudra alors utiliser une boucle pour vérifier si chaque valeur de la plage1 apparaît ou non dans la plage2 et comptabiliser le résultat au fur et à mesure du déroulement de la boucle.
Mais, encore une fois peut-être me trompe-je.
Attendons l'avis de VBAistes plus aguerris que moi.
A+

 

[nadir****]

Re : Traduire SOMMEPROD en VBA

Merci RepChecks.
Je crois effectivement que worksheetfunction.sumproduct ne répondra pas à mes attentes et que le mieux est de créer une fonction à appeler.
Encore merci pour ta longue réponse.
Nadir

 

[nadir****]

Re : Traduire SOMMEPROD en VBA

Merci à David aussi.
Je vais tenter d'écrire une fonction.
A+

 

[repcheks]

Re : Traduire SOMMEPROD en VBA

tu peux utiliser la fonction que j'ai écrit, en changeant juste les données

Set Plage1 = Range("B1:B9")
Set Plage2 = Range("C1:C9")

avec les plages que tu souhaites comparer, ca devrait marcher. Et pour le coup, elles n'ont meme pas besoin d'etre identiques, avantage par rapport a SommeProd.