Bonjour,
Alors, j'ai
Copier/Coller ton code dans un module et j'ai tout de suite eu une erreur de syntaxe avec cette ligne
nbdossiers = Evaluate("sumproduct((" & Plage1.Address& "="&Référence&") * ("&Plage2.address&" >= "&Seuil_Mini&") * ("&Plage2.Address&" <= "&Seuil_Maxi&))")
peut-être un problème de parenthèses. Je n'ai pas vraiment cherché.
La gestion des String dans une chaîne de caractères lorsque l'on doit insérer des variables ne facilite pas effectivement la lecture.
J'opte pour ma part pour des balises.
Les données
Pour mon test, j'ai une feuille nommée
bd contenant une liste de données à évaluer et une feuille nommée également
Reporting
Dans la feuille
bd, la plage contenant les montants se trouve en
E2:E101, les références (des type de logements) en
L2:L101.
Les paramètres se trouve sur la feuille
Reporting en cellule
B2, la valeur Minimum (pour l'exemple
2000), en
C2, la valeur Maximum (pour l'exemple
3000) et en
D2, la référence (pour l'exemple "
Villa"
La formule
Manuellement, la formule placée dans une cellule quelconque de la feuille
Reporting est
=SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)
La formule évaluée en VBA (sans les balises)
MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)")
La formule en VBA avec les balises
En vba, comme indiqué plus haut, lorsqu'il y a des paramètres, j'insère des balises (Exemple
<NomParametre>) que je remplace ensuite par les paramètres à l'aide de la fonction
Replace
donc la chaîne de caractères contenant la formule placée dans la variable
myFormula est pour notre exemple
"SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"
La procédure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| Sub TestEvaluate()
Dim rngMontant As Range, rngRef As Range
Dim Seuil_Mini As Double, Seuil_Maxi As Double, Reference As String
Dim myFormula As String
myFormula = "SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"
With ThisWorkbook.Worksheets("REPORTING")
Seuil_Mini = .Range("B2")
Seuil_Maxi = .Range("C2")
Reference = Chr(34) & .Range("D2") & Chr(34)
End With
With ThisWorkbook.Sheets("db")
Set rngMontant = .Range("$E$2:$E$101") 'Plage contenant les montants
Set rngRef = .Range("$L$2:$L$101") 'Plage contenant les références
End With
' Remplacement des balises
myFormula = Replace(myFormula, "<plageMontant>", rngMontant.Address(external:=True))
myFormula = Replace(myFormula, "<plageRef>", rngRef.Address(external:=True))
myFormula = Replace(myFormula, "<Ref>", Reference)
myFormula = Replace(myFormula, "<Mini>", Seuil_Mini)
myFormula = Replace(myFormula, "<Maxi>", Seuil_Maxi)
Debug.Print myFormula
' =SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)
'MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)")
MsgBox Evaluate(myFormula)
End Sub |
La formule telle qu'elle va être évaluée (après les remplacements)
Après le
Replace voici ce que donne la formule qui va être évaluée (variable
myFormula)
SUMPRODUCT( ('[Liste de données1]db'!$L$2:$L$101="Villa") * ('[Liste de données1]db'!$E$2:$E$101>=2000) * ('[Liste de données1]db'!$E$2:$E$101<=3000) * '[Liste de données1]db'!$E$2:$E$101)
Il reste à adapter avec ton cas précis mais ayant pris pratiquement le même exemple, je pense que cela sera relativement simple.
Dans l'exemple, les paramètres mis dans les variables
Seuil_Mini et
Seuil_Maxi ainsi que
Reference sont les valeurs des cellules comme dans ton code mais pour ma part, j'aurais opté pour les références aux cellules. (objet Range)
[EDIT]
J'ai rectifié la formule en ajoutant un bloc
With...End With (lignes 7 à 11)
Partager