[VBA] Petit souci de traduction de formule

7 réponses
Avatar
ThierryP
Bonjour le forum,

En colonne E, des références de produits, en colonne G, des prix.

En colonne I, je souhaite avoir le prix total des références qui commencent par "SR", donc j'écris :
=SOMMEPROD((GAUCHE(E2:E1000;2)="SR")*(G2:G1000))
Jusque lÍ , tout va bien, ça fonctionne !

Je souhaite insérer cette formule par VBA en utilisant Range("I1").FormulaR1C1
donc je passe Excel en style de références L1C1 et la formule devient :
=SOMMEPROD((GAUCHE(L(1)C(-4):L(999)C(-4);2)="SR")*(L(1)C(-2):L(999)C(-2)))
Je traduis SOMMEPROD par SUMPRODUCT, GAUCHE par LEFT, je change le ; par une , je double les guillemets et je colle tout ça dans mon VBA :
Range("I1").FormulaR1C1 = "=SUMPRODUCT((LEFT(L(1)C(-4):L(999)C(-4),2)=""SR"")*(L(1)C(-2):L(999)C(-2)))"

.... et lÍ , snif , "Erreur d'exécution"....
Qu'est-ce que j'ai loupé ????

Merci d'avance au généreux contributeur qui m'évitera de m'arracher les cheveux !!!

ThierryP

7 réponses

Avatar
Michel__D
Bonjour,
Le 20/10/2021 Í  15:35, ThierryP a écrit :
Bonjour le forum,
En colonne E, des références de produits, en colonne G, des prix.
En colonne I, je souhaite avoir le prix total des références qui commencent par "SR", donc j'écris :
=SOMMEPROD((GAUCHE(E2:E1000;2)="SR")*(G2:G1000))
Jusque lÍ , tout va bien, ça fonctionne !
Je souhaite insérer cette formule par VBA en utilisant Range("I1").FormulaR1C1
donc je passe Excel en style de références L1C1 et la formule devient :
=SOMMEPROD((GAUCHE(L(1)C(-4):L(999)C(-4);2)="SR")*(L(1)C(-2):L(999)C(-2)))
Je traduis SOMMEPROD par SUMPRODUCT, GAUCHE par LEFT, je change le ; par une , je double les guillemets et je colle tout ça dans mon VBA :
Range("I1").FormulaR1C1 = "=SUMPRODUCT((LEFT(L(1)C(-4):L(999)C(-4),2)=""SR"")*(L(1)C(-2):L(999)C(-2)))"
.... et lÍ , snif , "Erreur d'exécution"....
Qu'est-ce que j'ai loupé ????
Merci d'avance au généreux contributeur qui m'évitera de m'arracher les cheveux !!!
ThierryP

Quand tu veux faire ce genre de chose tu peux utiliser l'enregistreur de macro
et ensuite tu regarde le résultat obtenu cela peut donner des infos.
Avatar
MichD
Le 20/10/21 Í  09:35, ThierryP a écrit :
Bonjour le forum,
En colonne E, des références de produits, en colonne G, des prix.
En colonne I, je souhaite avoir le prix total des références qui commencent par "SR", donc j'écris :
=SOMMEPROD((GAUCHE(E2:E1000;2)="SR")*(G2:G1000))
Jusque lÍ , tout va bien, ça fonctionne !
Je souhaite insérer cette formule par VBA en utilisant Range("I1").FormulaR1C1
donc je passe Excel en style de références L1C1 et la formule devient :
=SOMMEPROD((GAUCHE(L(1)C(-4):L(999)C(-4);2)="SR")*(L(1)C(-2):L(999)C(-2)))
Je traduis SOMMEPROD par SUMPRODUCT, GAUCHE par LEFT, je change le ; par une , je double les guillemets et je colle tout ça dans mon VBA :
Range("I1").FormulaR1C1 = "=SUMPRODUCT((LEFT(L(1)C(-4):L(999)C(-4),2)=""SR"")*(L(1)C(-2):L(999)C(-2)))"
.... et lÍ , snif , "Erreur d'exécution"....
Qu'est-ce que j'ai loupé ????
Merci d'avance au généreux contributeur qui m'évitera de m'arracher les cheveux !!!
ThierryP

Bonjour,
Tu peux utiliser ceci :
Range("A2").FormulaLocal "=SOMMEPROD((GAUCHE(E2:E1000;2)=""SR"")*(G2:G1000))"
Je n'ai jamais utilisé le style de références L1C1 et il est trop tard
pour moi pour commencer.
Pourquoi tiens-tu Í  utiliser cela? Peut-être parce que tu as du temps Í 
perdre??? ;-))
MichD
Avatar
MichD
ou comme ceci :
Range("A3").Formula = "=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"
MichD
Avatar
MichD
Le 20/10/21 Í  11:03, MichD a écrit :
 ou comme ceci :
Range("A3").Formula = "=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"
MichD

La chinoiserie que tu veux avoir si la formule doit être inscrite en I2
Range("i2") = _
"=SUMPRODUCT((LEFT(RC[-5]:R[4]C[-3],2)=""SR"")*(RC[-4]:R[4]C[-2]))"
MichD
Avatar
MichD
Le 20/10/21 Í  12:44, MichD a écrit :
Le 20/10/21 Í  11:03, MichD a écrit :
  ou comme ceci :
Range("A3").Formula = "=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"
MichD

La chinoiserie que tu veux avoir si la formule doit être inscrite en I2
    Range("i2") = _
"=SUMPRODUCT((LEFT(RC[-5]:R[4]C[-3],2)=""SR"")*(RC[-4]:R[4]C[-2]))"
MichD

(RC[-5]:R[4]C[-3],2)
Le "-5" dans cette section de la formule signifie que la colonne est 5
colonnes Í  gauche de la cellule "i", soit la colonne D.
et dans cette section : (RC[-4]:R[4]C[-2]))"
Le "-4" signifie que la colonne est 4 colonnes Í  gauche de "i" soit la
colonne F.
Le moins que l'on puisse dire, cette syntaxe n'est pas évidente Í  lire
et Í  écrire...L'enregistreur de macro n'a pas le choix de la syntaxe
qu'il peut utiliser, mais les usagers OUI.
MichD
Avatar
ThierryP
Le mercredi 20 octobre 2021 Í  18:44:16 UTC+2, MichD a écrit :
Le 20/10/21 Í  11:03, MichD a écrit :
ou comme ceci :
Range("A3").Formula = "=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"
MichD
La chinoiserie que tu veux avoir si la formule doit être inscrite en I2
Range("i2") = _
"=SUMPRODUCT((LEFT(RC[-5]:R[4]C[-3],2)=""SR"")*(RC[-4]:R[4]C[-2]))"
MichD

C'est nickel, merci Denis !!
Avatar
MichD
Bonjour Denis,
Toujours fidèle au poste !!!
Le seul avantage, c'est qu'on peut mettre en variable numérique le numéro de ligne et de colonne, donc plus facile Í  manipuler si l'on souhaite trouver une cellule en fonction d'un calcul.
Les formules qui renvoient le numéro de colonne en fonction de la lettre sont un peu plus lourdes !
Mais j'avoue que je n'aime pas non plus !!

Voici un exemple avec seulement des variables. Rien ne t'empêche
d'utiliser des variables dans une formule plus traditionnelle comme :
"=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"
Ce code est beaucoup plus facile Í  lire et Í  interpréter. La difficulté
c'est souvent les guillemets...c'est une question de pratique et un peu
d'effort de compréhension!
'------------------------
Sub test()
Dim Rg As String
Dim Rg1 As String
Dim LastRow As Long
Dim T As String
T = "SR"
With Worksheets("Feuil1")
'Trouve la dernière ligne occupée de la colonne E
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
'Comme ce code est écrit pour être employé dans n'importe
'quel module, j'insère le nom de la feuille devant la
'plage de cellule
Rg = .Name & "!" & .Range("E2:E" & LastRow).Address
Rg1 = .Name & "!" & .Range("F2:F" & LastRow).Address
'Et tu choisis la cellule o͹ tu veux avoir le résultat.
.Range("A1") = "=SUMPRODUCT((left(" & Rg & ",2)=""" & T & """)*(" &
Rg1 & "))"
End With
End Sub
'------------------------
MichD