[VBA] Petit souci de traduction de formule

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.

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

ou comme ceci :

Range("A3").Formula = "=SUMPRODUCT((left(E2:E1000,2)=""SR"")*(G2:G1000))"

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

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

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

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