Fonction SOMMEPROD en VBA [Résolu]

Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
- - Dernière réponse : Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
- 7 nov. 2013 à 15:25
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.
Afficher la suite 

Votre réponse

5 réponses

Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
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.
Commenter la réponse de ucfoutu
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
0
Merci
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...
Commenter la réponse de Pauyr
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
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
Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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.
Commenter la réponse de ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
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
Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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 ?
jordane45
Messages postés
23610
Date d'inscription
mercredi 22 octobre 2003
Statut
Contributeur
Dernière intervention
14 décembre 2018
-
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)
ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
-
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.
Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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 ?
jordane45
Messages postés
23610
Date d'inscription
mercredi 22 octobre 2003
Statut
Contributeur
Dernière intervention
14 décembre 2018
-
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"...
Commenter la réponse de ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
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).

Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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 :)
ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
-
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.
Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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.
ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
-
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 !
Pauyr
Messages postés
27
Date d'inscription
dimanche 13 octobre 2013
Dernière intervention
7 novembre 2013
-
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 :)
Commenter la réponse de ucfoutu

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.