VBA : Insérer une formule dans une cellule Excel

[Miorphee]

Bonjour tout le monde,

je cherche à inscrire des formules dans les cellules excel via VBA pour ensuite pouvoir utiliser le solveur excel sur ces cellules.
Les formules étant très longues et ma variable calculée étant fonction de différents cas, je voulais commencer par entrer les 5 possibilités de calcul de ma variable dans 5 cellules excel :
Petit aperçu du type de formule :

ActiveSheet.Cells(5, 28).Formula = "((((((Sqr(1300#) - ActiveSheet.Cells(i, 16).Value) ^ 2) * 1300#) / (2# * 400# * 900#)) * 400#) / 1300#) + ((2# * ActiveSheet.Cells(1, 28) * Sqr(1300#) + 900#) / (2# * 900#)) - ((1 / (2# * 900#)) * (Sqr(1300#) - Sqr(((((Sqr(1300#) - ActiveSheet.Cells(i, 16).Value) ^ 2) * 1300#) / (2# * 400# * 900#)) * 2# * 400# * 900# / 1300#)) ^ 2)"

1ères questions à ce niveau:
Est-ce qu'il faut laisser le # pour que la dimension des nombres soit un Double ou est-ce inutile?
Faut-il utiliser le Range pour aller chercher les valeurs des cellules ou le ActiveSheet.cells.value fonctionne?
Pour la racine, c'est bien sqr?

Seconde partie :
Insérer une fonction si qui aille me chercher les bonnes formules en fonction des valeurs d'une autre variable:

For i = 2 To 178
ActiveSheet.Cells(i, 14).Formula = "=If(ActiveSheet.Cells(i, 15).Value <> "",If ((ActiveSheet.Cells(i, 16).Value) >= (ActiveSheet.Cells(i, 19) + ActiveSheet.Cells(1, 28)),ActiveSheet.Cells(4, 28),If (ActiveSheet.Cells(i, 16).Value >= ActiveSheet.Cells(i, 19).Value,ActiveSheet.Cells(5, 28),If (ActiveSheet.Cells(i, 16).Value >= (ActiveSheet.Cells(i, 20).Value + ActiveSheet.Cells(1, 28)),ActiveSheet.Cells(6, 28),If (ActiveSheet.Cells(i, 16).Value >= ActiveSheet.Cells(i, 20).Value,ActiveSheet.Cells(7, 28),If (ActiveSheet.Cells(i, 16).Value >= ActiveSheet.Cells(1, 28),ActiveSheet.Cells(8, 28),ActiveSheet.Cells(9, 28)))))))"
Next

Je n'ai pas trouvé quelle synthaxe utiliser pour appeler la fonction SI, pour le moment le mieux que j'ai réussi à fare c'est insérer les formules sous forme de texte!

Je n'ai pas l'habitude d'utiliser cette fonctionnalité de VBA alors si qqun pouvait me dépanner dans la synthaxe à utiliser je lui en serai très reconnnaissante

 

[Hippolite]

Re : VBA : Insérer une formule dans une cellule Excel

Bonjour,
Il faut travailler en chaine de caractères et avec les noms anglais des fonctions, par exemple :
Range("A" & Dlign + 1).formula = "= SUM(A2:A" & Dlign & ")"
A+

 

[JNP]

Re : VBA : Insérer une formule dans une cellule Excel

Bonjour le fil ,
La racine, c'est SQRT et non SQR. Le plus simple pour récupérer les formules en anglais, c'est de saisir ta formule en ayant lancé l'enregistreur de macro ...
Après, attention à bien réféchir à ce que tu veux dans ta formule ...
Si par exemple la valeur est en A1 (4) :

= "=SQRT(" & Cells(1,1) & ")"

va mettre dans la cellule

=RACINE(4)

alors que

= "=SQRT(" & Cells(1,1).Address & ")"

va mettre dans la cellule

=RACINE($A$1)

ce qui n'est pas la même ...
Bon courage

 

[Miorphee]

Re : VBA : Insérer une formule dans une cellule Excel

Merci pour les infos!
j'ai réécris mon code mais j'ai un nouveau problème :
Range("AB4").Formula = "=Sqrt(1300) - range(" & Cells(i, 16) & ") + 2 * range("AB1") "

me donne erreur de compilation: attendu: fin d'instruction

A priori ca vient du range("AB1"), j'ai beau fureté sur le forum, je vois pas ce qui cloche

 

[Hippolite]

Re : VBA : Insérer une formule dans une cellule Excel

Bonjour,
Pour mettre des guillemets dans la formule, il faut les doubler
range(""AB1"")
A+

 

[Miorphee]

Re : VBA : Insérer une formule dans une cellule Excel

Bonjour,

c'est noté, je me méfierai des guillemets !
J'ai fait un crash test et bien sur ca a crashé!
Mon code ressemble à ceci (en version simplifiée):

Range("AB4").Formula = "=(Sqrt(1300) - Range(P" & i & ")) ^ 2 + (2 * AB1))"

Petite remparque: Ecrire range(""AB1"") dans la formule me fait apparaitre range("AB1") dans la cellule et excel ne va pas chercher la valeur correspondante, j'ai donc remplacé par AB1 directement.

Quand je clique dans la cellule où s'insère cette formule, j'ai bien les deux cellules appelées qui apparaissent en couleur (exemple: si i=9, P9 est coloriée, AB1 aussi) pourtant ma celllule affiche #NOM?
Quand je faisais le calcul sous VBA, il fallait que je définisse mes variables en temps que Double et pareil pour les nombres que je mettais dans le calcul, est-ce que ca peut venir de la même chose?

 

[Miorphee]

Re : VBA : Insérer une formule dans une cellule Excel

Illumination de dernière minute, en supprimant le range dans la formule ca marche, dsl de ce message inutile !

 

[Hippolite]

Re : VBA : Insérer une formule dans une cellule Excel

Re,
Il faut supprimer "Range" et il y a une fermeture de parenthèse de trop
A+

 

[Miorphee]

Re : VBA : Insérer une formule dans une cellule Excel

Merci pour le débugage VBA, ca marche !

Maintenant que mon programme tourne, je me rend compte que le résultat n'est pas celui escompté. Pour essayer de faire simple, je voulais programmer 5 formules dans 5 cases excel, sachant que chaque formule correspond au calcul d'une même variable selon la condition appliquée (exemple si y > N alors on utilise la formule de la case AB4).
Range("AB4").Formula = "=(Sqrt(1300) - P" & i & ") ^ 2) + (2 * AB1 * Sqrt(1300))"
Range("AB5").Formula = " mon autre formule...
...

puis je voulais faire une boucle pour calculer la valeur de ma variable en appelant la formule adaptée au cas rencontré

For i = 2 To 178
Range("N" & i).Formula = "=If(O" & i & " = """","""",If (P" & i & " >= S" & i & "+AB1,AB4,If (P" & i & " >= (S" & i & ") ,AB5,If (P" & i & " >= (T" & i & "+ AB1),AB6,If (P" & i & " >= (T" & i & "),AB7,If (P" & i & " >= AB1,AB8,AB9))))))"
Next

Le problème c'est qu'avec mon programme toute ma colonne N me renvoit la dernière valeur de variable (celle correspondant à i =178).
Y a-t-il un moyen pour que le i de ma formule corresponde à la ligne ou se fait le calcul et que sa valeur ne change pas tant que je reste sur cette ligne?

Mon objectif final est d'utiliser le solveur sur la cellule AB1 pour que les valeurs de la colonne N se rapproche le plus possible des valeurs mesurées de la colonne à côté.

Je ne suis pas sure d'avoir été claire donc n'hésitez pas à me demander plus d'explications !

En tout cas merci de prendre le temps de lire ma prise de tête du jour

 

[JNP]

Re : VBA : Insérer une formule dans une cellule Excel

Re ,
Je ne comprends pas bien ton problème ...
Après avoir supprimé tous les espaces qui faisaient planter le code , j'obtiens bien en N2

=SI(O2 = "";"";SI(P2>=S2+AB1;AB4;SI(P2>=(S2);AB5;SI(P2>=(T2+AB1);AB6;SI(P2>=(T2);AB7;SI(P2>=AB1;AB8;AB9))))))

et en N178

=SI(O178 = "";"";SI(P178>=S178+AB1;AB4;SI(P178>=(S178);AB5;SI(P178>=(T178+AB1);AB6;SI(P178>=(T178);AB7;SI(P178>=AB1;AB8;AB9))))))

Que voulais-tu obtenir ?
A +

 

[Miorphee]

Re : VBA : Insérer une formule dans une cellule Excel

Effectivement j'ai copié la version du code avec espace, toutes mes excuses!
J'ai exactement la même chose que toi, ce qui ne va pas c'est que la valeur de AB4 (et AB5 ...) est dépendante de i, si je mets les formules en dehors de mon for, i n'est pas défini et j'obtiens des #NOM partout, si je mets les formules dans ma boucle, la dernière valeur prise par AB4 est pour i =178, du coup toutes mes cellules de N affiche les valeurs pour i = 178.

J'ai en partie contourné le problème en mettant directement les formules de AB4, AB5 et autres à la place de AB4, AB5 dans le SI. Ca fait très très lourd comme formule mais à défault de mieux pour le moment ca marche !

J'espère que c'était plus clair !

 

[JNP]

Re : VBA : Insérer une formule dans une cellule Excel

Re ,
Je pense que c'est la meilleure solution, car tu ne peux pas avoir en AB1 par exemple une formule variable en fonction de la ligne que tu remplis ... Tu ne peux pas lier des formules en aller/retour ...
Bises et bon courage