Transformation formule en code vba

Version imprimable

Bonjour à tous

Actuellement j'insère cette formule :

Code:

=STXT(D12;1;SI(SIERREUR(CHERCHE(" ";D12;1)-1;"Erreur")="Erreur";NBCAR(D12);CHERCHE(" ";D12;1)-1))

Par le biais d'une macro :
Code:

1 2 ActiveCell.FormulaR1C1 = _ "=MID(RC[-2],1,IF(IFERROR(SEARCH("" "",RC[-2],1)-1,""Erreur"")=""Erreur"",LEN(RC[-2]),SEARCH("" "",RC[-2],1)-1))"
J'essaye de comprendre comment ne plus insérer la formule mais vers "la fonction" dans la macro.
J'ai donc créer une p'tite macro de test :
Code:

1 2 3 4 5 6 7 8 9 10 Sub test() Dim Valeure_reference As String Dim Vz As Integer Vz = 12 Valeure_reference = mid(cells(vz, 4),1, if(IfError(Search(" ", Cells(Vz, 4), 1) - 1, "Error") = "Error", len(cells(vz, 4)), Search(" ", Cells(Vz, 4), 1) - 1)) MsgBox Valeure_reference End Sub
Cependant elle ne compile pas :

Citation:

"Erreur de compilation : Erreur de syntaxe"

Merci par avance

04/09/2014, 14h44
parmi

Bonjour,

Essaie comme ceci

Code:

Valeure_reference = "mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))"
04/09/2014, 14h55
r.morel

1 pièce(s) jointe(s)

Bonjour,
Merci pour votre réponse.

Lorsque j'essaye le code il me renvoie :
Pièce jointe 155402

Cordialement.
04/09/2014, 15h12
parmi

Si tu veux le résultat de la formule, affecte celle-ci à une cellule

Code:

ActiveCell.Formula = "mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))"

Et change ActiveCell selon le besoin

Code:

Range("A1").Formula = "mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))"
04/09/2014, 15h23
r.morel

Cela rempli la cellule avec le texte présent dans la MsgBox évoquée lors de mon message précédent.

En fait je cherche si il y a un moyen de "faire le calcul" dans la macro et de renvoyer le résultat dans la cellule.

Je vous remercie par avance.
04/09/2014, 15h34
pc75

Bonjour,

Tu peux expliquer précisément ce que fait ta formule ?
04/09/2014, 15h47
Invité

Bonjour,

Code:

Range("A1").Value = Evaluate("mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))")
04/09/2014, 15h56
Philippe Tulliez
Bonjour,
Avant tout, à la lecture de ta formule, il me semble que tu extrais les caractères qui se trouvent à gauche du premier caractère espacé d'une chaîne de caractères et dans l'hypothèse où il n'y aurait pas ce caractère tu prends l'entièreté de cette chaîne.
En supposant que la cellule de référence soit C2, on pourrait plutôt utiliser cette formule

Code:

=SIERREUR(GAUCHE(C2;CHERCHE(" ";C2)-1);GAUCHE(C2;NBCAR(C2)))

qui traduite en anglais (seul langage comprit par VBA) donnerait

Code:

=IFERROR(LEFT(C2,SEARCH(" ",C2)-1),LEFT(C2,LEN(C2)))

En VBA, la formule devant être insérée dans une variable de type String (pour l'exemple une constante nommée myFormula) nous obligerait à placer un double-quote pour la recherche d'un espace comme ci-dessous

Code:

Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH("" "",C2)-1),LEFT(C2,LEN(C2)))"

nous allons utiliser la recherche du code ASCII 20 qui est le code équivalent d'un espace. Soit

Code:

Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH(CHAR(32),C2)-1),LEFT(C2,LEN(C2)))"

La formule étant en place, comment la traduire en VBA pour ne plus l'insérer comme formule.
La plus simple est de passer par la fonction EVALUATE
Exemple
Code:

1 2 Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH(CHAR(32),C2)-1),LEFT(C2,LEN(C2)))" MsgBox Evaluate(myFormula)
ou
Code:

1 2 Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH(CHAR(32),C2)-1),LEFT(C2,LEN(C2)))" Range("I2") = Evaluate(myFormula)
Tu peux aussi placer la formule dans une plage de cellules et ensuite faire l'équivalent d'un copier/collage spécial Valeur
Code:

1 2 3 Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH(CHAR(32),C2)-1),LEFT(C2,LEN(C2)))" Range("I2:I6").Formula = myFormula Range("I2:I6").Value = Range("I2:I6").Value
Dans les exemples ci-dessus, seul la référence aux cellules est précisée mais il est chaudement recommandé de référencer le classeur et la feuille également.
Exemple
Code:

1 2 3 4 5 Const myFormula$ = "=IFERROR(LEFT(C2,SEARCH(CHAR(32),C2)-1),LEFT(C2,LEN(C2)))" With ThisWorkbook.Worksheets("Feuil1").Range("I2:I6") .Formula = myFormula .Value = .Value End With
04/09/2014, 16h24
r.morel

Merci Philippe pour ces explications. J'apprend encore plein de choses aujourd'hui... ;)
Je vais utiliser la méthode du copier et coller avec les valeurs puisque j'ai besoin de la fonction complète.

J'avais déjà utiliser ce subterfuge dans une autre macro. Mais à cause de mon petit niveau en VBA je trouvais cette méthode pas très "propre".

Je retiendrais les autres solutions pour d'autres applications.

Merci à vous pour votre temps dans ce super site...
Bonne fin de journée. :D
04/09/2014, 17h01
parmi

Je vois que tu as eu pas mal de réponses...

J'avais oublié le "="

Code:

ActiveCell.Formula = "=mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))"
04/09/2014, 17h06
r.morel

Citation:

Envoyé par parmi

Je vois que tu as eu pas mal de réponses...

J'avais oublié le "="

ActiveCell.Formula = "=mid(" & Cells(Vz, 4) & ",1, if(IfError(Search("" "", " & Cells(Vz, 4) & ", 1) - 1, ""Error"") = ""Error"", len(" & Cells(Vz, 4) & "), Search("" "", " & Cells(Vz, 4) & ", 1) - 1))"

Je l'avais vu mais lorsque je l'ai rajouté la macro ne compilait plus.
Merci.