WorksheetFunction.SumProduct (VBA)

[Kotov]

Bonsoir à tous,

Je prépare actuellement une macro qui sort plus de 500 données issues d'une base et je coince bêtement sur WorksheetFunction.SumProduct.

J'utilise régulièrement sans problème SOMMEPROD en formules avec de multiples conditions mais je bloque sur la même fonction en VBA.

Vous trouverez ci-après une macro expurgée posant le problème sachant que
- Plage contient les valeurs "Oui" ; "Non" et ""
- Plage1 contient des données numériques négatives (ex : -1234,56)
et que bien évidemment je cherche à connaître le montant global des valeurs numériques se référant à la plage contenant les valeurs "Oui"

Sub StatsGlob()
Dim r&
Dim ShR As Worksheet
Set ShR = ActiveWorkbook.Sheets("R")
Dim ShS As Worksheet
Set ShS = ActiveWorkbook.Sheets("STATS")
Dim Plage As Range
Dim Plage1 As Range

ShR.Activate
'*****************  Calculs avec SUMPRODUCT  ************************
With ShR
r = ShR.Cells(65536, 1).End(3).Row

Set Plage = ShR.Range(Cells(2, 69), Cells(r, 69))
Set Plage1 = ShR.Range(Cells(2, 68), Cells(r, 68))
  
 
[color=blue] '3 tentatives sans succès : [/color]

[B]ShS.Cells(9, 8) = Application.WorksheetFunction.SumProduct(Plage.Value = "Oui" * 1, Plage1.Value)[/B] 'j'obtiens "erreur 13"

[B]ShS.Cells(10, 8) = Application.WorksheetFunction.SumProduct(Plage, Plage1)[/b] 'Résultat=0, si je fais SumProduct(Plage,1, Plage1) j'obtiens "erreur 13"

[B]ShS.Cells(11, 8) = Application.WorksheetFunction.SumProduct(Plage.Value = "Oui" ,1, Plage1.Value)[/B] 'j'obtiens "erreur 13"


'******* .... Autres calculs (sans intérêt pour le problème posé)  ****************
End With
'*****************  Libération de mémoire  ************************
Set ShR = Nothing
Set ShR = Nothing
Set Plage = Nothing
Set Plage1 = Nothing
End Sub

Pour ce soir j'ai le cerveau en surchauffe, alors peut être que quelqu'un verra au premier coup d'oeil sur quoi je bute depuis une heure.

Bonne soirée
Kotov


EDIT : bien sûr, je peux obtenir le résultat avec une boucle qui additionne les montants quand la valeur est "Oui", mais j'aimerais l'obtenir avec WorksheetFunction.SumProduct

 

[Cousinhub]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir, vite fait, sans analyse :la fonction Sommeprod (ou Sumproduct) doit avoir 2 plages de taille identiques (toi :
Set Plage = ShR.Range(Cells(2, 69), Cells(r, 69))
Set Plage1 = ShR.Range(Cells(2, 68), Cells(r, 68))

Il manque une colonne, non?

Mais, juste vite fait!!!

Edit : annule, moi aussi en surchauffe

 

[Spitnolan08]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir Kotov, bhbh

Ton explication bhbh concerne cette instruction :

SumProduct(Plage,1, Plage1)

mais pas les autres.

Par contre, je pense que ce que n'accepte pas VBA c'est le

Plage.Value = "Oui" * 1

Mais je ne sais pas résoudre ton problème avec un sommprod...

Cordialement

 

[Cousinhub]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir, Spitnolan08, jai annulé mon post, pas évident de comparer 2 plages de deux feuilles différentes avec un sommeprod. Je regarde. Bonne soirée

 

[Spitnolan08]

Re : WorksheetFunction.SumProduct (VBA)

Re,

 Edit : annule, moi aussi en surchauffe

Je crois que tu es en dessous de la vérité bhbh😉 car Plage1 et Plage sont sur la même page... Il n'y a donc pas 2 matrices sur 2 pages différentes... Ou alors j'ai trop bu...

Cordialement

 

[Cousinhub]

Re : WorksheetFunction.SumProduct (VBA)

Bien ce que je disais, en surchauffe§§

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour Spit, Bhbh, le forum,

Merci de vous être penché sur mon problème.
Je n'ai pas trouvé, pour l'instant, la solution.

Je confirme bien que les données sont sur la même feuille et que les plages à comparer sont strictement identiques.

Bien sûr, comme je l'ai dit dans mon post d'origine, je peux obtenir le résultat en bouclant sur les 2 plages et en additionnant les valeurs numériques de la colonne 68 si la valeur de la colonne 69 = "Oui" ....mais y'a pas de raison : si VBA propose la fonction SumProduct, c'est qu'elle est utile et fonctionnelle !
Reste à trouver comment l'utiliser , car quand on voit ce qu'on peut faire avec SOMMEPROD en formules, on doit pouvoir faire également des miracles avec SUMPRODUCT en VBA.

Je continue à chercher .. de toute façon il pleut !! 😉

Bonne journée à tous
Kotov

 

[chris]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour
As tu essayé de doubler les parenthèses
Application.WorksheetFunction.SumProduct((Plage.Value = "Oui") * 1, Plage1.Value)

 

[Cousinhub]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour, cette formule fonctionne très bien, mais il faut que la zone nommée provienne de la feuille. Je m'explique : lorsque je nomme les zones Plage et Plage1 directement dans la feuille (Insertion.....), ça marche. Si elle est définie dans ton code : Plouf
y = [SumProduct(plage1 * (plage = "OUI"))]
Une piste
(C'est mieux après une bonne nuit)

 

[Spitnolan08]

Re : WorksheetFunction.SumProduct (VBA)

Re, Bonjour Chris,

As tu essayé de doubler les parenthèses
Application.WorksheetFunction.SumProduct((Plage.Value = "Oui") * 1, Plage1.Value)

Moi je l'avais essayé mais nada...
Par contre Kotov, pourrais tu mettre un petit fichier avec 1 feuille, 2 colonnes dito ta BD et dans une cellule la formule sur la feuille Excel, car je n'arrive pas à la faire fonctionner sans VBA, donc il m'est difficile d'aller plus loin si je ne comprends pas ce que je dois faire.

Bien cordialement

 

[ninbihan]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour Kotov, Bhbh, Spit et Chris,

Il semble que sumproduct n'aime pas les RANGE, j'ai essayé ceci

Sub test()
Dim tablo() As Variant
Dim tablo1() As Variant
tablo = Range("A1", "A6").Value
tablo1 = Range("B1", "B6").Value
MsgBox (WorksheetFunction.SumProduct(tablo, tablo1))
End Sub

Mais bien sur cela ne fonctionne qu'avec des valeurs numériques, je n'arrive pas à reconnaitre le "oui". Je continue à chercher car comme dis Kotov, Il pleut (encore !!).

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir à tous
Merci Bhbh, Spit, Chris et Ninbihan pour vos tentatives.

Pressé par le temps, j'ai obtenu le résultat souhaité par le biais d'une boucle mais si j'ai réglé le problème de façon pratique, il 'nest pas résolu sur le plan théorique (et je n'aime pas quand ça résiste !)
Y'a pas de raison que cette fonction, si performante en formule, soit "bridée" en VBA et ne puisse être utilisée avec du texte.

Comme Ninbihan, je pressens des problèmes avec les plages (Range, matrice ..) mais également avec le Type de variable.

Je joins un exemple le plus basique possible. Il sera toujours temps de compliquer plus tard.

Pour info :
- les données sont vérifiées (toutes numériques dans la colonne A, et soit "Oui" ou "Non" dans la colonne B). Parfois les erreurs proviennent de saisies erronées.
- j'avais essayé les différentes positions de parenthèses, sans succès.


Devant m'absenter, je me pencherai sur le problème qu'en fin de soirée.
A +
Kotov

 

[myDearFriend!]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir Kotov, bhbh, Spitnolan08, chris, ninbihan, le Forum,

Tu dois pouvoir faire comme ça :

[SIZE=2]ShS.Cells(2, 2) =[B] Evaluate([/B]"SumProduct((" & Plage.Address & "= ""Oui"") * 1, " & Plage1.Address & ")"[B]) [/B][/SIZE]

Ceci pour éviter une boucle...
A ma connaissance, on ne peut pas y arriver par "WorksheetFunction.SumProduct()"

Cordialement,

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Grand Merci MyDearFriend!,

J'adopte ta proposition avec Evaluate. C'est fonctionnel, c'est court et ça évite une boucle.
Quelques recherches sur le Net me laissent également l'impression que la fonction WorksheetFunction.SumProduct (en VBA) n'est pas à la hauteur de sa petite soeur en formule, "Magic" SOMPROD ! .
Dommage, mais je vais bien étudier les spécificités d'Evaluate qui doivent résoudre de nombreux problèmes liés à la sélection de plages de données.

Merci également à Bhbh, Spitnolan08, Chris, Ninbihan qui se sont penchés sur le problème.

Bonne nuit à tous
Kotov

 

[Spitnolan08]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour Kotov, bhbh, myDearFriend,

J'ai trouvé le moyen d'utilise'r ton sumproduct en VBA, certes en trichant un peu... Mais plus de boucle! J'y avais pensé mais je n'arrivais pas à le mettre en forme et grâce à une intervention de bhbh sur un autre fil, c'est bon :

Sub StatsGlob()
Dim r&
Dim ShR As Worksheet
Set ShR = ActiveWorkbook.Sheets("R")
Dim ShS As Worksheet
Set ShS = ActiveWorkbook.Sheets("STATS")
Dim Plage As Range
Dim Plage1 As Range

ShR.Activate
'*****************  Calculs avec SUMPRODUCT  ************************
With ShR
r = ShR.Cells(65536, 1).End(3).Row

Set Plage = ShR.Range(Cells(2, 2), Cells(r, 2))
Set Plage1 = ShR.Range(Cells(2, 1), Cells(r, 1))
Plage.Replace What:="Oui", Replacement:=1, LookAt:=xlPart
Plage.Replace What:="Non", Replacement:=0, LookAt:=xlPart

ShS.Cells(2, 2) = Application.WorksheetFunction.SumProduct(Plage, Plage1) 'Résultat=0
ShS.Cells(2, 2) = Application.WorksheetFunction.SumProduct(Plage.Value, Plage1.Value)

Plage.Replace What:=1, Replacement:="Oui", LookAt:=xlPart
Plage.Replace What:=0, Replacement:="Non", LookAt:=xlPart
End With
'*****************  Libération de mémoire  ************************
Set ShR = Nothing
Set ShS = Nothing
Set Plage = Nothing
Set Plage1 = Nothing

End Sub

Yesssssssssssssssssssssssssssssss!

Bien cordialement