XL 2013 Entrer une Formule dans une cellule en VBA

[Paninak]

Bonjour au forum

J'aimerais pouvoir rentrer une formule dans une cellule en VBA. La formule a été écrite par un pro (Jocelyn) et fonctionne très bien pour mon problème.
Le but est de pouvoir rentrer la formule en VBA dans chaque cellule d'une colonne en changeant au fur et à mesure le "A1" dans la formule en "A2" etc. "i" remplacerait le numéro de ligne.
J'ai essayé plein de guillemets surement mal placés car le compilateur attend toujours quelque chose.

Formule ci dessous

=(SIERREUR(DATE(STXT(A1;CHERCHE(",";A1)+2;4);MOIS(GAUCHE(A1;CHERCHE(" ";A1)-1)&1);STXT(A1;CHERCHE(" ";A1)+1;CHERCHE(",";A1)-(CHERCHE(" ";A1)+1)))+SI(ET(TEMPSVAL(STXT(A1;CHERCHE(":";A1)-2;2)&":"&STXT(A1;CHERCHE(":";A1)+1;2))>"11:59"*1;ESTNUM(CHERCHE("avant";A1)));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;6))-("12:00"*1);SI(OU(ET(TEMPSVAL(STXT(A1;CHERCHE(":";A1)-2;2)&":"&STXT(A1;CHERCHE(":";A1)+1;2))>"11:59"*1;ESTNUM(CHERCHE("ap";A1)));ESTNUM(CHERCHE("avant";A1)));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;6));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;6))+("12:00"*1)));DATE(STXT(A1;CHERCHE(",";A1)+2;4);MOIS(GAUCHE(A1;CHERCHE(" ";A1)-1)&1);STXT(A1;CHERCHE(" ";A1)+1;CHERCHE(",";A1)-(CHERCHE(" ";A1)+1)))+SI(ET(TEMPSVAL(STXT(A1;CHERCHE(":";A1)-2;2)&":"&STXT(A1;CHERCHE(":";A1)+1;2))>"11:59"*1;ESTNUM(CHERCHE("avant";A1)));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;5))-("12:00"*1);SI(OU(ET(TEMPSVAL(STXT(A1;CHERCHE(":";A1)-2;2)&":"&STXT(A1;CHERCHE(":";A1)+1;2))>"11:59"*1;ESTNUM(CHERCHE("ap";A1)));ESTNUM(CHERCHE("avant";A1)));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;5));TEMPSVAL(STXT(A1;CHERCHE(",";A1)+6;5))+("12:00"*1)))))*1

 

[Hieu]

Re : Entrer une Formule dans une cellule en VBA

Salut,

En copiant ta formule dans une cellule excel, celle-ci indique des erreurs.

Sinon, j'aurai essayé quelque chose comme ça :

Sub lkjlk()
Range("g" & i).FormulaLocal = _
"=(SIERREUR(DATE(STXT(A" & i & ",CHERCHE(A" & i & ")+2,4)," & _
"MOIS(GAUCHE(A" & i & ",CHERCHE(" ",A" & i & ")-1)&1)," & _
 "STXT(A" & i & ",CHERCHE(" ",A" & i & ")+1," & _
...
' attention aux ";" qui doivent etre remplacés par des ","
End Sub

Possibilité 2 :
Pourquoi ne tires-tu pas simplement ta formule ?

Possibilité 3 :
pourquoi ne fais-tu pas un CTRL+C CTRL+V en macro de la cellule qui fonctionne vers celles que tu souhaites ??

++
Hieu

 

[Lone-wolf]

Re : Entrer une Formule dans une cellule en VBA

Bonjour paninak, Hieu

Tu peux aussi faire comme ceci, un exemple:
Ceci est ce que dit Hieu en proposition 3 mais en vba.

Sub test()
With Feuil1
Feuil3.Range("a1").Copy ' Feuille masquée avec cellule contenant la formule
.Range("c1").PasteSpecial Paste:=xlPasteFormulas
Range("c1").AutoFill Range("c1:c31"), Type:=xlFillDefault
End With
End Sub

 

[Paninak]

Re : Entrer une Formule dans une cellule en VBA

Bonjour le forum et merci pour ta réponse Hieu.

Afin de mieux visualiser la fonction de la formule, je joins un fichier.
Dans ma feuille, la formule fonctionne car la colonne A est renseignée. Sinon, cela génère un : #VALEUR , comme sur la dernière ligne.

Pour répondre à Hieu, j'ai bien pensé utiliser le code d'une macro en faisant un copier/coller, mais cela implique que la formule est écrite dans une cellule de la feuille. Or le fichier va être utiliser par d'autres personnes ne connaissant pas du tout Excel. Si quelqu'un efface la cellule ou supprime la feuille, cela ne fonctionnera plus. Je préfère garder le code hors de portée, dans un module.

Ce qui est étrange, c'est que d'une part la formule fonctionne très bien quand elle est collée dans une cellule, mais dès que je l'écris dans le code, cela s'affiche en rouge, même si je me contente de mettre un guillemet au début et un autre à la fin comme si j'affectais une chaine String. Pour faire rentrer la formule en code, je dois la couper avec un " _".

Le soupçon que j'ai est que la formule utilise des mots code provenant d'un complément que je n'aurais pas. Je ne sais pas si c'est possible puisque ça fonctionne sur la feuille.

Si JOCELYN, l'auteur de la formule pouvait m'éclairer.

 

[Lone-wolf]

Re : Entrer une Formule dans une cellule en VBA

Re Paninak

Si tu as peur que l'on supprime la feuille. Crée un nouveau classeur en y copiant la formule(si possible sur clé USB). Ensuite dans un module tu crée une macro qui copie la feuille de ton classeur dans la feuille du classeur de destination avec la macro que j'ai mis. Ce que tu peux faire aussi, dans thisworkbook_beforeclose: activeworkbook.SaveAs Filename:= xxxx.xlsx par exemple.

 

[Paninak]

Re : Entrer une Formule dans une cellule en VBA

Lone-Wolf
Merci de t'intéresser au problème. Mais ta solution complique les choses. Il n'y a pas plus simple ? Cette formule doit bien pouvoir se copier en VBA.

 

[Lone-wolf]

Re : Entrer une Formule dans une cellule en VBA

Lone-Wolf
Merci de t'intéresser au problème. Mais ta solution complique les choses. Il n'y a pas plus simple ? Cette formule doit bien pouvoir se copier en VBA.

Pourquoi elle compliquerait la chose ?? Plus simple que ça... . Vu la complexité de la formule, je ne vois pas comment...
Et pour éviter d'avoir #Valeur dans les cellules tu ajoute ceci à la formule: =SI(A1="";"";TA FORMULE)). Et tu exécute la macro que j'ai mis.

 

[mapomme]

Re : Entrer une Formule dans une cellule en VBA

Bonjour Paninak, Lone-wolf,

Un essai via VBA dans le fichier qui permet de placer la formule n'importe où et pas seulement dans la colonne G.

Le principe:

On a créé une fonction MaFormule(xCell As Range) dont le paramètre est la cellule qui contient le texte source (ie la cellule qui joue le rôle de A1) et qui retourne le texte de la formule.

On y a définit une variable contenant la formule où A1 a été remplacé par le texte "µµ"

Cette fonction remplace simplement le texte "µµ" de la variable Formule par l'adresse de la la cellule xCell.

Au sein du code VBA, pour introduire la formule dans la cellule K5 avec la cellule source en N5, on écrira :
Cells(5,"N").FormulaLocal=MaFormule( Cells(5,"K") )


Pour le test, cliquer sur le bouton "Placer Formules"


Code de la fonction MaFormule dans le module mod_global :

Function MaFormule(xCell As Range) As String
'-
'- retourne la formule à introduire
'- xcell est la cellule source
'-
Dim formule As String, adresse As String

' Définition de la formule (l'adrese A1 a été remplacée par le texte µµ)
formule = _
  "=(SIERREUR(DATE(STXT(µµ;CHERCHE("","";µµ)+2;4);MOIS(GAUCHE(µµ;CHERCHE("" "";µµ)-1)&1);STXT(µµ;CHERCHE("" "";µµ)+1;CHERCHE("","";µµ)-(" & _
  "CHERCHE("" "";µµ)+1)))+SI(ET(TEMPSVAL(STXT(µµ;CHERCHE("":"";µµ)-2;2)&"":""&STXT(µµ;CHERCHE("":"";µµ)+1;2))>""11:59""*1;ESTNUM(CHERCHE(" & _
  """avant"";µµ)));TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;6))-(""12:00""*1);SI(OU(ET(TEMPSVAL(STXT(µµ;CHERCHE("":"";µµ)-2;2)&"":""&STXT(µµ;" & _
  "CHERCHE("":"";µµ)+1;2))>""11:59""*1;ESTNUM(CHERCHE(""ap"";µµ)));ESTNUM(CHERCHE(""avant"";µµ)));TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;6));" & _
  "TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;6))+(""12:00""*1)));DATE(STXT(µµ;CHERCHE("","";µµ)+2;4);MOIS(GAUCHE(µµ;CHERCHE("" "";µµ)-1)&1);" & _
  "STXT(µµ;CHERCHE("" "";µµ)+1;CHERCHE("","";µµ)-(CHERCHE("" "";µµ)+1)))+SI(ET(TEMPSVAL(STXT(µµ;CHERCHE("":"";µµ)-2;2)&"":""&STXT(µµ;" & _
  "CHERCHE("":"";µµ)+1;2))>""11:59""*1;ESTNUM(CHERCHE(""avant"";µµ)));TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;5))-(""12:00""*1);SI(OU(ET(" & _
  "TEMPSVAL(STXT(µµ;CHERCHE("":"";µµ)-2;2)&"":""&STXT(µµ;CHERCHE("":"";µµ)+1;2))>""11:59""*1;ESTNUM(CHERCHE(""ap"";µµ)));ESTNUM(CHERCHE" & _
  "(""avant"";µµ)));TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;5));TEMPSVAL(STXT(µµ;CHERCHE("","";µµ)+6;5))+(""12:00""*1)))))*1"

' le texte µµ est remplacé par l'adresse de la cellule source
' au format Lettre-Chiffre et relatif (sans signe $)
MaFormule = Replace(formule, "µµ", xCell(1, 1).Address(0, 0, xlA1))
End Function

Code de la routine TEST dans le module mod_test :

Sub TEST()
Dim i&

  With Sheets("Feuil1")
    For i = 2 To 37
      .Cells(i, "g").FormulaLocal = MaFormule(.Cells(i, "a"))
      Halte 150     'à supprimer
    Next i
    
    For i = 2 To 37
      .Cells(i, "n").FormulaLocal = MaFormule(.Cells(i, "k"))
      Halte 150     'à supprimer
    Next i
  End With
End Sub

 

[Paninak]

Re : Entrer une Formule dans une cellule en VBA

Merci Mapomme

Cela a l'air de fonctionner sur ton exemple. Je vais étudier ça de plus près, car il me faudra plus de temps pour comprendre le code qu'il ne t'en a fallu pour l'écrire.

 

[Lone-wolf]

Re : Entrer une Formule dans une cellule en VBA

Rebonjour mapomme

 

[bof]

Re : Entrer une Formule dans une cellule en VBA

Bonjour,
Le plus simple est d'enregistrer ta formule dans le gestionnaire de nom :
Tu copie la formule de G1.
Tout en gardant cette cellule sélectionnée, tu la copies dans le Gestionnaire de nom en lui donnant... le nom que tu veux ! Dans mon exemple j'ai choisi "SUBSTFRM" mais tu peux mettre "libellule" ou "papillon" ça fera le même effet....
Ensuite tu écris ta macro comme j'ai fait.
C'est tout.

Cerise sur le gâteau : la formule subsiste même en cas de suppression de la feuille ou de la colonne !
A+

 

[Paninak]

Re : Entrer une Formule dans une cellule en VBA

Merci bof, c'est une solution simple et fonctionnelle. Parfait pour mon problème.
Encore merci au forum.

 

[Paninak]

Re : Entrer une Formule dans une cellule en VBA

Finalement, je suis revenu à la solution de mapomme qui fonctionne parfaitement aussi.
Le problème que j'avais avec la solution de la formule dans le gestionnaire, c'est qu'une fois utilisée, cela ne fonctionnait plus parce que les adresse avaient changées. Je n'ai pas trop compris pourquoi aussi ai-je essayé la solution de mapomme qui fonctionne.
>Encore merci à tous.

 

[bof]

Re : Entrer une Formule dans une cellule en VBA

re,
j'ai modifié ta formule dans la cellule G1 pour qu'elle s'adapte à toutes les colonnes.
C'est cette formule que tu dois enregistrer dans le gestionnaire de nom sous le nom de ton choix.
Comme le montre le fichier joint la même formule donne le même résultat quelle que soit la colonne...
A+