Fonction SOMMEPROD en VBA

Résolu

Pauyr Messages postés 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024 - 6 nov. 2013 à 14:28
Pauyr Messages postés 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024 - 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.

A voir également:

Sommeprod vba
Sommeprod(-- - Meilleures réponses
Somme prod - Meilleures réponses
Doevents vba - Conseils pratiques -Visual Basic / VB.NET
Byval vba - Conseils pratiques -Visual Basic / VB.NET
Aide vba - Conseils pratiques -Visual Basic / VB.NET
Vba modulo - Forum Visual Basic
Le reste d'une division - Forum Visual Basic 6

5 réponses

Réponse 1 / 5

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
6 nov. 2013 à 14:44

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.

Réponse 2 / 5

Pauyr Messages postés 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
6 nov. 2013 à 15:09

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

Réponse 3 / 5

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
Modifié par ucfoutu le 6/11/2013 à 17:28

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
7 nov. 2013 à 08:19

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.

Réponse 4 / 5

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
Modifié par ucfoutu le 7/11/2013 à 08:35

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
7 nov. 2013 à 09:24

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 38145 Date d'inscription mercredi 22 octobre 2003 Statut Modérateur Dernière intervention 25 avril 2024 344
7 nov. 2013 à 09:53

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 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
Modifié par ucfoutu le 7/11/2013 à 10:59

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
Modifié par Pauyr le 7/11/2013 à 11:04

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 38145 Date d'inscription mercredi 22 octobre 2003 Statut Modérateur Dernière intervention 25 avril 2024 344
7 nov. 2013 à 11:32

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

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 5

ucfoutu Messages postés 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
7 nov. 2013 à 11:19

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
7 nov. 2013 à 11:37

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 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
Modifié par ucfoutu le 7/11/2013 à 13:34

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
7 nov. 2013 à 14:12

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 18038 Date d'inscription lundi 7 décembre 2009 Statut Modérateur Dernière intervention 11 avril 2018 211
7 nov. 2013 à 14:44

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 58 Date d'inscription dimanche 13 octobre 2013 Statut Membre Dernière intervention 24 avril 2024
7 nov. 2013 à 15:25

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 :)

Discussions similaires

Comment fonctionne la fonction FormulaR1C1

fonction modulo en VBA

Dernière cellule non vide d'une colonne

A quoi sert la fonction Set ?

Le Modulo en VB6

Votre réponse

Discussions similaires