Discussion :

[IAmByB]

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 :

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

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 :

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

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 :

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

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 :

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

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

[Philippe Tulliez]

Bonjour,

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.

[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.

[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.

[tedo01]

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

[IAmByB]

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 ?

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 ?

[Philippe Tulliez]

Bonjour,
Tedo01

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

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 :

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

=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.

[IAmByB]

Alors là, je dois remercier tedo01 pour ses conseils. En effet, le problème venait du fait que dans la ligne :

Code :

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

1
2
 
Formule = "=IF(" & Formule2 & ">0;" & Formule3 & ";0)"

j'utilisais des points virgule, alors que la ligne qui fonctionne est :

Code :

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

1
2
 
Formule = "=IF(" & Formule2 & ">0," & Formule3 & ",0)"

(avec des virgules).

Merci beaucoup !!! C'est comme ça qu'on apprend.

[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 :

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

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