Traduction dune formule excel en VBA

[mathbou73]

Bonjour à tous,

je souhaiterai connaitre la traduction de la formule excel ci-dessous en VBA car lorsque j'essayer l'enregistreur de macro en me positionnant sur la cellule contenant cette formule et que je fais F2 et validation, j'obtiens le message "impossible d'enregistrer".
Apparemment en recherchent sur la toile, cela est du à certaines limites sous excel quant à l'utilisation de l'enregistreur de macro ou au nombre de caractéres contenu dans une cellule

voici la formule

=SI(ET(D38<>"";H38<>"";U38<>"");SI(X38<=M38;"";SI(ET(W38<M38;X38>M38;X38<=N38);X38-M38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX)); SI(ET(W38<M38;X38>N38);P38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX));SI(ET(W38>=M38;X38<=N38);Y38;SI(ET(W38>=M38;W38<N38;X38>N38);N38-W38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX));SI(W38>=N38;"";"Cas non traité"))))));"")

pour info j'ai essayé de decomposer en 2 partie ma formules (afin d'analyser le code VBA généré) mais là encore l'enregistreur de macro me retourne le même message

exemple de decomposition

partie 1

=SI(ET(D38<>"";H38<>"";U38<>"");SI(X38<=M38;"";SI(ET(W38<M38;X38>M38;X38<=N38);X38-M38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX));"suite")))

partie 2

=SI(ET(W38<M38;X38>N38);P38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX));SI(ET(W38>=M38;X38<=N38);Y38;SI(ET(W38>=M38;W38<N38;X38>N38);N38-W38-SI(ET(U38<>"";U38="oui");RECHERCHEV(H38;Vacations;11;FAUX));SI(W38>=N38;"";"Cas non traité"))))

merci d'avance pour votre aide

 

[Caillou]

Re : Traduction dune formule excel en VBA

Bonjour,

Tu peux toujours utiliser la propriété FormulaLocal pour écrire la formule telle-que
Attention toutefois au guillemets, dans vba la formule sera considérée comme une chaine de caractères (donc entre guillemets) ce qui t'oblige à doubler tous les guillemets de ta formule
Ce qui donne :

ActiveCell.FormulaLocal = "=SI(ET(D38<>"""";H38<>"""";U38<>"""");SI(X38<=M38;"""";SI(ET(W38<M38;X38>M38;X38<=N38);X38-M38-SI(ET(U38<>"""";U38=""oui"");RECHERCHEV(H38;Vacations;11;FAUX));SI(ET(W38<M38;X38>N38);P38-SI(ET(U38<>"""";U38=""oui"");RECHERCHEV(H38;Vacations; 11;FAUX));SI(ET(W38>=M38;X38<=N38);Y38;SI(ET(W38>= M38;W38<N38;X38>N38);N38-W38-SI(ET(U38<>"""";U38=""oui"");RECHERCHEV(H38;Vacations;11;FAUX));SI(W38>=N38;"""";""Cas non traité""))))));"""")"

Mais je suis pas sûr d'avoir bien compris, ni d'avoir été très clair !

Caillou

 

[Raja]

Re : Traduction dune formule excel en VBA

Bonjour,

Avant d'aller plus loin, vu la longueur de la formule et vu encore la composition de la formule (tout se passe dans la ligne 38), je te préconise tout d'abord de simplifier la formule. En général, une formule trop longue ne facilite pas la compréhension de celle-ci. Il y a sûrement d'autres possibilités de trouver le résultat voulu.

Pour cela merci de nous joindre ton fichier vidé des données confidentielles. Je suis persuadé que nous trouverons une solution adaptée à ton attente.

 

[mathbou73]

Re : Traduction dune formule excel en VBA

Tout d'abord merci pour votre réactivité,
je vais essayer de voir avec la solution de Caillou (FormulaLocal).
Si je ne m'en sors pas je reviendrai vers vous et joidrai alors un fichier exemple.

Merci encore, je vous tiens au courant

 

[job75]

Re : Traduction dune formule excel en VBA

Bonjour à tous,

Supposons que cette formule soit en cellule A1.

- Menu Outils-Options-Général et cocher Style de référence L1C1. On obtient la formule :

=SI(ET(L(37)C(3)<>"";L(37)C(7)<>"";L(37)C(20)<>"");SI(L(37)C(23)<=L(37)C(12);"";SI(ET(L(37)C(22)<L(37)C(12);L(37)C(23)>L(37)C(12);L(37)C(23)<=L(37)C(13));L(37)C(23)-L(37)C(12)-SI(ET(L(37)C(20)<>"";L(37)C(20)="oui");RECHERCHEV(L(37)C(7);Vacations; 11;FAUX)); SI(ET(L(37)C(22)<L(37)C(12);L(37)C(23)>L(37)C(13));L(37)C(15)-SI(ET(L(37)C(20)<>"";L(37)C(20)="oui");RECHERCHEV(L(37)C(7);Vacations; 11;FAUX));SI(ET(L(37)C(22)>=L(37)C(12);L(37)C(23)<=L(37)C(13));L(37)C(24);SI(ET(L(37)C(22)>= L(37)C(12);L(37)C(22)<L(37)C(13);L(37)C(23)>L(37)C(13));L(37)C(13)-L(37)C(22)-SI(ET(L(37)C(20)<>"";L(37)C(20)="oui");RECHERCHEV(L(37)C(7);Vacations; 11;FAUX));SI(L(37)C(22)>=L(37)C(13);"";"Cas non traité"))))));"")

- Copier la cellule A1 et coller au même endroit (A1) dans une feuille Macro Internationale Excel 4.0 que vous aurez insérée. On obtient la formule :

=IF(AND(R[37]C[3]<>"",R[37]C[7]<>"",R[37]C[20]<>""),IF(R[37]C[23]<=R[37]C[12],"",IF(AND(R[37]C[22]<R[37]C[12],R[37]C[23]>R[37]C[12],R[37]C[23]<=R[37]C[13]),R[37]C[23]-R[37]C[12]-IF(AND(R[37]C[20]<>"",R[37]C[20]="oui"),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)), IF(AND(R[37]C[22]<R[37]C[12],R[37]C[23]>R[37]C[13]),R[37]C[15]-IF(AND(R[37]C[20]<>"",R[37]C[20]="oui"),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)),IF(AND(R[37]C[22]>=R[37]C[12],R[37]C[23]<=R[37]C[13]),R[37]C[24],IF(AND(R[37]C[22]>= R[37]C[12],R[37]C[22]<R[37]C[13],R[37]C[23]>R[37]C[13]),R[37]C[13]-R[37]C[22]-IF(AND(R[37]C[20]<>"",R[37]C[20]="oui"),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)),IF(R[37]C[22]>=R[37]C[13],"","Cas non traité")))))),"")

- Mettre une apostrophe devant la formule puis par menu Edition-Remplacer, remplacer les guillemets simples " par des doubles "", puis mettre l'ensemble entre guillemets après avoir enlevé l'apostrophe.

- Enfin introduire cette expression dans VBA pour obtenir finalement le code :

Sub Macro1()
Range("A1").FormulaR1C1 = "=IF(AND(R[37]C[3]<>"""",R[37]C[7]<>"""",R[37]C[20]<>""""),IF(R[37]C[23]<=R[37]C[12],"""",IF(AND(R[37]C[22]<R[37]C[12],R[37]C[23]>R[37]C[12],R[37]C[23]<=R[37]C[13]),R[37]C[23]-R[37]C[12]-IF(AND(R[37]C[20]<>"""",R[37]C[20]=""oui""),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)), IF(AND(R[37]C[22]<R[37]C[12],R[37]C[23]>R[37]C[13]),R[37]C[15]-IF(AND(R[37]C[20]<>"""",R[37]C[20]=""oui""),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)),IF(AND(R[37]C[22]>=R[37]C[12],R[37]C[23]<=R[37]C[13]),R[37]C[24],IF(AND(R[37]C[22]>= R[37]C[12],R[37]C[22]<R[37]C[13],R[37]C[23]>R[37]C[13]),R[37]C[13]-R[37]C[22]-IF(AND(R[37]C[20]<>"""",R[37]C[20]=""oui""),VLOOKUP(R[37]C[7],Vacations, 11,FALSE)),IF(R[37]C[22]>=R[37]C[13],"""",""Cas non traité"")))))),"""")"
End Sub

- décocher la case Style de référence L1C1, effacer A1 et lancer la macro pour vérifier.

Je reconnais que tout ça est un peu lourd...

A+

A+

 

[mathbou73]

Re : Traduction dune formule excel en VBA

Merci à tous,

j'ai réussi à m'en sortir avec la soluce de Caillou (FormulaLocal). Par contre j'en profite pour demander si cette solution peut aussi s'appliquer à des formules de type matricielle car je rencontre aussi le message d'erreur avec l'enregistreur de macro sur des cellules contenant ce type de formule. Je vais essayer d'appliquer aussi cette méthode et on verra bien.

Bon week end à tous