Fonction SOMMEPROD en VBA [Résolu]

Signaler
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019
-
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019
-
Bonjour,

J'aimerai utiliser la fonction SOMMEPROD en VBA. J'ai compris comment elle marchait, elle fonctionne sur ma feuille mais je souhaiterais l'incorporer au code VBA qui me génère la dite feuille.

Sur la feuille Excel, la formule ressemble à

=SOMMEPROD((H11:H40="Berline 1")*(G11:G40)*(D11:D40))

Le but est d'intégrer cette formule à une boucle car le terme "Berline 1" va varier dans la boucle. J'ai défini un vecteur " modele(n) " où modele = array(X valeurs).

Je suis passé dans l'enregistreur de macro qui me sort :

Range("F" & n).FormulaR1C1 = "=SUMPRODUCT((R[9]C[2]:R[38]C[2]=""Berline 1"")*(R[9]C[1]:R[38]C[1])*(R[9]C[-2]:R[38]C[-2]))"


J'ai toute les peines du monde à modifier ce bout de code (je voudrais que le "38" soit remplacé par quelque chose qui me donne la dernière ligne non vide ; faire varier "Berline 1", etc..).

Je suis également passé par Application.WorksheetFunction.SumProduct mais il n'accepte pas la syntaxe avec les étoiles (tel que sur la feuille excel).

J'espère avoir été assez clair et merci d'avance de votre aide.

5 réponses

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
Bonjour,
Une formule n'est qu'une chaîne de caractères.
Ton problème, tel que présenté, n'est pas celui d'une formule de de la construction d'une chaîne de caractères par concaténation de texte et de variable(s) à mettre entre guillemets.
Voilà donc ma manière de te répondre.
Test :
toto = "blabla ""10"""
MsgBox toto
titi = "10"
toto = "blabla " & """" & titi & """"
MsgBox toto


A toi de comprendre et d'adapter à TA chaine de caractères.
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

Salut ucfoutu,

Il me semble que j'ai compris à quoi servent les & et les double guillemets (sans outrecuidance aucune) mais je ne crois pas que cela réponde à mon problème ; ou alors je ne vois pas où tu veux en venir.

J'ai "réussi" à convertir des NB.SI et des SOMME.SI en VBA car la syntaxe était la même ; là mon VBA (via application.worksheetfunction.sumproduct) n'accepte pas les "*" entre les ranges que je veux manipuler, il m'indique que je dois les séparer par des ",".

Quant à ce que m'indique l'enregistreur de macro, je trouve cela un peu trop bricolage et ça ne m'aide pas tellement...
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
Le problème est-il d'écrure en VBA ou d'écrire ta formule ?
En d'autres termes :
Quelle est la formule qui "marche" si écrite manuellement depuis Excel ?
Es-tu au moins passé par cette étape ?
Montre cette formule et dis-nous
- ce qui doit y être remplacé par une variable (chaque élément de la formule) et par quelle valeur.
PAR EXEMPLE : Dans ta formule :
=SOMMEPROD((H11:H40="Berline 1")*(G11:G40)*(D11:D40))
j'ai compris que tu voulais remplacer "Berline 1" par le contenu d'une variable
H par quoi ?
G et D par quoi ?
11 et 40 par quoi ?

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

Mince, mon commentaire d'hier soir ne semble pas être passé ... je disais donc :

Mon problème est bien d'écrire cette formule en VBA. La formule qui marche, écrite dans une cellule Excel est bien celle que j'ai mise :
=SOMMEPROD((H11:H40="Berline 1")*(G11:G40)*(D11:D40))

Je ne souhaite pas remplacer H, G ou D par quelque chose. Je veux faire la somme des produits G(i) * D(i) quand H(i) correspond à ce qui est dans ma variable.

À la limite, remplacer "40" par la dernière ligne non-vide mais j'avais essayé de rempalcer "40" par "& Range("A" & Rows.Count).End(xlUp).Row" sans succès .. Mais ce n'est pas le plus important.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
C'est bien ce que je te disais ===>> problème de traitement d'une chaîne de caractères avec utilisation de variables (que la chaine soit ou non une formule) .

Regarde ce que fait ceci (sur la cellule F1 dans cet exemple) :

Dim toto As String, ou As Long
toto = "Berline 1"
ou = Range("A" & Rows.Count).End(xlUp).Row
Range("F1").Formula = "=SUMPRODUCT((H11:H" & ou & "=""" & toto & """)*(G11:G" & ou & ")*(D11:D" & ou & "))"


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

En effet cela écrit la formule correcte dans la case F1.
Quelle est alors la différence entre Range.Formula et Application.WorksheetFunction ?

Le premier m'a l'air beaucoup plus maniable puisqu'il vient écrire la formule à appliquer dans la cellule de la feuille et le second bah .. je sais pas trop, il te calcule une valeur que tu peux manipuler par la suite ?
Messages postés
32480
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
15 mai 2021
347
Bonjour,
Range.Formula, permet d'écrire une formule dans une feuille Excel.
WorkSheetFunction permet d'utiliser une fonction excel dans ton code VBA au même titre que tu utiliserais dans ton code des + , des - , des / ..etc... pour effectuer des calculs par exemple.(sans l'écrire dans ton classeur)
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
Le choix entre l'utilisation d'une formule ou celle d'un calcul fait par worksheetfunction dépend avant tout de ce que tu dois ensuite faire.
Pourquoi ?
par exemple et entre autres parce-que la modification par formule de la valeur d'une cellule n'entraîne pas le déclenchement de l'évènement Worksheet_change. Il permet par contre un calcul plus rapide et toujours mis à jour.
Le choix de l'utilisation de WorkSheetFunction déclenche, lui, l'évènement WorkSheetChange (si tu dois l'utiliser), mais t'oblige à relancer ce calcul(code) à chaque fois que le besoin s'en fait sentir (modif d'une valeur, etc ...)
A partir de là ===>> tu es bien seul à être en mesure de faire ce choix, en fonction de ce que fait ton appli ici et là.
Voilà.
Si, maintenant, ton problème est résolu, libère cette discussion en cliquant sur le tag RESOLU au niveau de ton tout 1er message.

NB : dès que tu l'auras fait, je modifierai le titre de ta discussion.


PS : A propos de ce que j'ai dit en ce qui concerne le non-déclenchement de l'évènement WorkSheet_Change lorsque le contenu d'une cellule est modifié par Formule :
Ainsi que j'ai déjà eu l'occasion de l'exposer, il faut comprendre que tous les calculs Excel (formules, donc) se font dans un fil (thread) distinct de celui de VBA. Les modifications de valeurs ainsi faites échappent donc totalement à VBA.
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

Résolu ? Oui et non, je ne sais toujours pas comment correctement écrire avec le WorksheetFunction notamment pour "prendre en compte" les * que je peux mettre dans une cellule ou en passant par Formula.

Mais j'ai trouvé deux alternatives qui me satisfont, donc oui de ce côté-là.

Sinon, entre temps j'avais trouvé une autre solution qui consistait à passer par un
Range("F" & n) = Application.Evaluate("SUMPRODUCT((H11:H3000=""" _
& modele(n) & """)*(G11:G3000)*(D11:D3000))")

C'est plus ou moins la même chose que de passer par Formula, non ?
Messages postés
32480
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
15 mai 2021
347
Si tu souhaites écrire la formule (sommeprod) dans ta cellule, utilise la méthode proposée par Ucfoutu.

Si tu souhaites n'y mettre que le résultat du calcul il faut, à ce moment là, passer par EVALUATE(sumproduct... ).
En effet, l'utilisation "directe" de expression.SumProduct ne tolère pas l'utilisation de syntaxes "alternatives"...
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
1) Pourquoi passer par Evaluate (lourd et inutile) ? Quelle en est la raison ?
2) le code que je t'ai montré prend en compte le * (qui multiplie).

Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

1) C'est ce j'avais trouvé de mieux en attendant de comprendre ce qui me tracassait. Et je ne savais pas que Evaluate est "lourd et inutile" :)

2) Oui, nickel ! Mais je ne sais toujours pas l'écrire dans un WorksheetFunction. Bref je pourrais vivre sans, ta solution me paraît tout aussi efficace. Merci :)
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
Je crois que l'on/tu mélange(s) un peu tout, maintenant.
Question (réponds-y très clairement) :
Que souhaites-tu avoir dans la cellule concernée, exactement ? :
1) une formule (qui y affiches un résultat)
2) une valeur calculée (pas de formule, donc)
Réponds à cette question précise après avoir relu mon message de 10 h 39 qui fait état de ces différences importantes.
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

J'ai bien lu ton message de 10h39 et peu m'importe la méthode tant que ça marche. Ce que je veux dire, c'est que je comprends la différence entre la formule qui affiche un résultat dans une cellule et une cellule où j'affiche une valeur calculée mais ça revient au même pour moi (désolé si cela te froisse :) !).

La page où l'on consulte les résultats (quelque soit la méthode) est créée à partir d'une macro où, après création de la page et import des données me servant aux calculs, je souhaite que les résultats du somprod s'affichent.

Ce que j'essaie de dire, c'est que les données du tableau pour les calculs ne bougent pas dynamiquement. Je peux altérer les données qui vont y être importées, depuis une autre feuille, mais il n'y a pas de lien. Je remettrai le tableau à jour en relançant la macro (qui va supprimer la feuille créée, la recrée et réimporte les valeurs (oui, c'est probablement pas le plus efficace ..)).

Je ne sais pas si je suis clair ; dans tous les cas, là, "ça marche" donc je peux appliquer le tag résolu si tu le souhaites.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
236
Ce que je veux dire, c'est que je comprends la différence entre la formule qui affiche un résultat dans une cellule et une cellule où j'affiche une valeur calculée mais ça revient au même pour moi (désolé si cela te froisse :) !).
Je te l'ai pourtant exposé avec précision !
Messages postés
38
Date d'inscription
dimanche 13 octobre 2013
Statut
Membre
Dernière intervention
26 avril 2019

Non mais j'ai compris la distinction (tu as peut-être lu "que je ne comprends pas") :)

Merci pour les réponses en tout cas, je tag résolu :)