XL 2013 Entrer une Formule dans une cellule en VBA

Paninak · 20 Mai 2016

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 · 21 Mai 2016

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 :

Code:

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 · 21 Mai 2016

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.

Code:

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 · 21 Mai 2016

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 · 21 Mai 2016

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 · 21 Mai 2016

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 · 21 Mai 2016

Re : Entrer une Formule dans une cellule en VBA

Paninak à dit:
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 · 21 Mai 2016

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 :

VB:

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 :

VB:

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 · 21 Mai 2016

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 · 21 Mai 2016

Re : Entrer une Formule dans une cellule en VBA

Rebonjour mapomme 🙂

bof · 21 Mai 2016

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 · 21 Mai 2016

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 · 21 Mai 2016

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 · 21 Mai 2016

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+

XL 2013 Entrer une Formule dans une cellule en VBA

Paninak

XLDnaute Nouveau

Hieu

XLDnaute Impliqué

Lone-wolf

XLDnaute Barbatruc

Paninak

XLDnaute Nouveau

Pièces jointes

Lone-wolf

XLDnaute Barbatruc

Paninak

XLDnaute Nouveau

Lone-wolf

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Paninak

XLDnaute Nouveau

Lone-wolf

XLDnaute Barbatruc

bof

XLDnaute Occasionnel

Pièces jointes

Paninak

XLDnaute Nouveau

Paninak

XLDnaute Nouveau

bof

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

XL 2013 Entrer une Formule dans une cellule en VBA

XLDnaute Nouveau

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Occasionnel

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency