Problème sur une macro créée en VBA

Version imprimable

Bonjour,

C'est encore moi avec une application VBA qui doit insérer une formule dans une case d'un fichier Excel.

Mon code est le suivant :
Code:

1 2 3 4 5 6 Formule2 = "SUMPRODUCT((DI" & CStr(Debut) & ":DI" & CStr(Fin) & ">0)*(DG" & CStr(Debut) & ":DG" & CStr(Fin) & "=" & Quote & "IRP" & Quote & "))" Formule3 = "(SUM(CC" & CStr(Debut) & ":CC" & CStr(Fin) & ")-SUM(DI" & CStr(Debut) & ":DI" & CStr(Fin) & "))/" & Formule2 Formule = "=IF(" & Formule2 & ">0;" & Formule3 & ";0)" .Range(sColAI_IRP & Fin + indexLigne).Formula = Formule .Range(sColAI_IRP & Fin + indexLigne).NumberFormat = "0.00%"
Debut et Fin sont des valeurs numériques.
Formule, Formule2 et Formule3 sont des chaines de caractères.
sColAI_IRP est une chaine de caractères qui vaut "BO"

Le code ci-dessus construit (dans la variable Formule) la macro suivante :
Code:

1 2 =IF(SUMPRODUCT((DI362:DI382>0)*(DG362:DG382="IRP"))>0;(SUM(CC362:CC382)-SUM(DI362:DI382))/SUMPRODUCT((DI362:DI382>0)*(DG362:DG382="IRP"));0)
J'ai copié la macro dans le bloc note, j'ai remplacé SUMPRODUCT par SOMMEPROD, SUM par SOMME et IF par SI. J'obtiens :
Code:

1 2 =SI(SOMMEPROD((DI362:DI382>0)*(DG362:DG382="IRP"))>0;(SOMME(CC362:CC382)-SOMME(DI362:DI382))/SOMMEPROD((DI362:DI382>0)*(DG362:DG382="IRP"));0)
et si je colle cette version "traduite" dans une case de mon fichier Excel, elle marche très bien.

Malheureusement, quand j'exécute le code VBA donné ci-dessus (qui construit la même macro dans la variable Formule, avec les noms des fonctions en anglais), j'ai une erreur 1004 ("Erreur définie par l'application ou l'objet") qui se déclenche sur la ligne :
Code:

1 2 .Range(sColAI_IRP & Fin + indexLigne).Formula = Formule
c'est à dire quand j'affecte la macro à une case de ma feuille Excel.
Pourtant, j'ai vérifié qu'elle marchait très bien quand je la colle moi même dans un fichier Excel.

Si vous avez une idée qui expliquerait ce comportement, merci de me la soumettre. Pour ma part, j'y ai passé plus d'une heure sans succès ...

17/04/2014, 17h51
Philippe Tulliez

Bonjour,

Citation:

Malheureusement, quand j'exécute le code VBA donné ci-dessus (qui construit la même macro dans la variable Formule, avec les noms des fonctions en anglais), j'ai une erreur qui se déclenche sur la ligne :

Quand tu demandes de l'aide et que tu reçois un message d'erreur, il n'est pas superflu de donner le code et le message d'erreur.

Je vois au moins deux problèmes
Que renvoie ce calcul sColAI_IRP & Fin + indexLigne vraisemblablement pas une référence correcte et de plus ta formule utilise des références relatives or il me semble que tu insères la formule en une fois sur plusieurs cellules. Si c'ets le cas, il faut utiliser des références absolues.
17/04/2014, 17h55
tedo01

Bonjour,
Pour passer du français à l'anglais, il ne suffit pas de traduire les fonctions. Il faut aussi remplacer les point-virgules par des virgules pour séparer les paramètres :-)
Une autre solution plus simple consiste à utiliser la méthode FormulaLocale et à mettre dedans la formule en français.
17/04/2014, 17h59
IAmByB

J'ai rectifié mon message après coup pour indiquer le type d'erreur (1004) que je reçois.

Par ailleurs, le calcul :

sColAI_IRP & Fin + indexLigne

renvoie "B0" & 382 + 4 soit "BO386" dans un fichier qui contient plus de 800 lignes et dont les colonnes vont de "A" à "DI".

Je génère la macro avec l'aide de plusieurs variables (Formule, Formule2 et Formule3), mais je ne l'insert que dans une seule case, à savoir "BO382". Je n'ai pas compris ta remarque sur les références relatives ou absolues ...

Il faut dire que dans mon programme, un code quasiment similaire permet d'insérer d'autres formules à d'autres endroits du fichier, et que tout se passe bien.
17/04/2014, 18h03
tedo01

Re,
Est-ce que tu as essayé ma solution ?
17/04/2014, 18h10
IAmByB

Citation:

Envoyé par tedo01

Bonjour,
Pour passer du français à l'anglais, il ne suffit pas de traduire les fonctions. Il faut aussi remplacer les point-virgules par des virgules pour séparer les paramètres :-)
Une autre solution plus simple consiste à utiliser la méthode FormulaLocale et à mettre dedans la formule en français.

Mais alors pourquoi la version française donnée dans mon premier message (avec des point virgules) fonctionne lorsque je la colle moi même dans le fichier Excel ?

Citation:

Envoyé par tedo01

Re,
Est-ce que tu as essayé ma solution ?

Pas encore, car j'ai dans le code d'autres macro construites de la meme façon et insérées de la même manière dans le fichier Excel, et cela fonctionne parfaitement. J'aimerais donc comprendre d'où vient le problème plutôt que de traiter d'une façon spécifique une macro qui devrait fonctionner comme les autres.

Mais si je n'arrive pas à comprendre d'où vient le problème, cette suggestion m'aidera peut être à résodre mon problème ?
17/04/2014, 18h12
Philippe Tulliez

Bonjour,
Tedo01

Citation:

Pour passer du français à l'anglais, il ne suffit pas de traduire les fonctions. Il faut aussi remplacer les point-virgules par des virgules pour séparer les paramètres :-)

Celle là m'avait échappée.

IAmByB

Citation:

Je n'ai pas compris ta remarque sur les références relatives ou absolues ...

SOMMEPROD fait référence à plusieurs cellule. par exemple

Code:

=SOMMEPROD( (A2:A100="Ville") * 1)

A2:A100 est une référence relative alors que $A$2:$A$100 est une référence absolue.
Si tu devais recopier la formule sur plusieurs cellules il y aurait lieu de référencer la plage de manière absolue.
Il y aussi les références mixtes comme $A2:$A100 et A$2:A$100.
17/04/2014, 18h20
IAmByB
Alors là, je dois remercier tedo01 pour ses conseils. En effet, le problème venait du fait que dans la ligne :
Code:

1 2 Formule = "=IF(" & Formule2 & ">0;" & Formule3 & ";0)"
j'utilisais des points virgule, alors que la ligne qui fonctionne est :
Code:

1 2 Formule = "=IF(" & Formule2 & ">0," & Formule3 & ",0)"
(avec des virgules).

Merci beaucoup !!! C'est comme ça qu'on apprend.
17/04/2014, 18h29
tedo01
re,
Merci pour le retour.
A noter que la syntaxe suivante marcherait aussi, ce qui peut être plus simple si on part d'une formule existante en Excel pour la coder en VBA :
Code:

1 2 Formule = "=SI(" & Formule2 & ">0;" & Formule3 & ";0)" .Range(sColAI_IRP & Fin + indexLigne).FormulaLocal = Formule