Discussion :

[IAmByB]

Bonjour,

Dans un programme VBA destiné à insérer des formules de calcul dans un document Excel, j'ai utilisé le code suivant (extrait) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Dim Quote
Dim Formule As String
Dim Formule2 as String
 
Quote = """"
 
With maFeuille
Formule = "=SUMPRODUCT((BJ" & CStr(Debut) & ":BJ" & CStr(Fin) & "=" & Quote & "-1" & Quote & ")*(BN" & CStr(Debut) & ":BN" & CStr(Fin) & ">0))" 
Formule2 = "=SUMPRODUCT(BJ" & CStr(Debut) & ":BJ" & CStr(Fin) & "=" & Quote & "-1" & Quote & ")"
.Range(sCol05 & Fin + indexLigne).Formula = Formule & "/" & Formule2
.Range(sCol05 & Fin + indexLigne).NumberFormat = "0.00%"
.Range(sCol05 & Fin + indexLigne).Font.ColorIndex = 3  ' Rouge
End With

Le but de la formule est d'effectuer le calcul suivant :
Compter le nombre de lignes où la colonne BJ contient "-1" et où la colonne BN contient une valeur > 0.
Diviser ce résultat par le nombre de lignes où la colonne BJ contient "-1"

Si je n'utilise pas le contenu de Formule 2 en remplaçant la ligne qui affecte la formule par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
.Range(sCol05 & Fin + indexLigne).Formula = Formule

alors j'obtiens un résultat numérique cohérent.

Par contre, si je garde la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
.Range(sCol05 & Fin + indexLigne).Formula = Formule & "/" & Formule2

j'obtiens un message indiquant une division par zéro dans la formule, ce qui semble prouver que la formule contenue dans Formule2 retourne 0.

Pouvez vous m'indiquer d'où vient le problème, car il me semble pourtant que le contenu de Formule2 est une formule tout à fait légitime (pour info, SUMPRODUCT correspond en français à SOMMEPROD) ?

Merci de votre aide.

[Philippe Tulliez]

Bonjour,
Avant de codifier une formule avec du VBA, tu devrais d'abord tester la formule sans le VBA
Ta variable formule2 contient =SUMPRODUCT(BJ:BJ="-1") or pour compter le nombre de valeurs trouvée il faut au moins multiplier par 1, soit en en Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((BJ:BJ=-1) * 1)

Donc ta formule renvoie 0 et évidemment, diviser par 0 renvoie une erreur.

Attention qu'il y a peut-être un problème aussi dans formule. Je n'ai regardé que formule2

[IAmByB]

Je te remercie pour cette info précieuse (je ne suis pas un spécialiste des macros Excel).

J'ai donc modifié mon code avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Formule2 = "=SUMPRODUCT((BJ" & CStr(Debut) & ":BJ" & CStr(Fin) & "=" & Quote & "-1" & Quote & ") * 1)"
.Range(sCol05 & Fin + indexLigne).Formula = Formule & "/" & Formule2

Quand je teste la formule directement sous Excel avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((BJ2:BJ162="-1")*(BN2:BN162>0))/SOMMEPROD((BJ2:BJ162="-1")*1)

cela marche parfaitement.

Malheureusement, j'ai maintenant l'erreur N° 1004 ("erreur définie par l'application ou par l'objet") à l'exécution de la dernière ligne VBA ci-dessus. Sans doute une erreur de syntaxe ?

[IAmByB]

Bon, j'ai trouvé. Il faut juste enlever le signe "=" qui débute le contenu de la variable Formule2. C'était idiot, mais il fallait le trouver ... ;-)

Merci à Philippe Tulliez.

[Philippe Tulliez]

Bonjour,
Je ne sais pas si tu dois placer ta formule sur une ou plusieurs cellules mais si c'est sur plusieurs cellules il y a lieu de rendre absolu la référence à la plage $BJ$2:$BJ$162