Comment écrire une formule dans une plage de cellules en VBA ?

Introduction

Question fréquemment posée dans les forums est l’insertion de formule à l’aide d’une procédure VBA.
Une façon simple d’intégrer une formule dans un code VBA est l’utilisation de l’enregistreur de macros. Cependant l’enregistreur génère la formule en style R1C1 ce qui n’est pas toujours simple à gérer.
Le plus simple est d’écrire la formule dans excel et de la lire ensuite à l’aide d’une simple instruction en utilisant la propriété Formula de l’objet Range.

Comment écrire une formule simple dans une cellule ?

Exemple 1 :

Prenons un exemple en plaçant en cellules A2 et B2 un nombre quelconque et en cellule C2, une formule qui renvoie le produit de la valeur de ces deux cellules.

Les différentes étapes illustrées plus bas sont :

En cellule C2 la formule =A2*B2
Dans la fenêtre d’exécution de l’éditeur VBA (Ctrl+G), nous taperons l’instruction « ? Range("C2").Formula » en la validant par la touche Entrée (Enter). « ? » est une commande raccourcie pour l’instruction Debug.Print)
Le code va donner =A2*B2 qu’il suffira d’intégrer dans une instruction VBA en utilisant la propriété Formula de l’objet Range en lui passant la formule comme valeur Alphanumérique.
Soit

Range("C2").Formula = "=A2*B2"

Exemple 2 :

Nous allons maintenant, écrire une fonction de base d’excel, la somme d’une plage en ligne ($B$2 :$G$2) que l’on placera en cellule H2
Répétons les étapes 1 à 3 décrites plus haut.

Ecriture de la formule =SOMME(B2:G2) en H2
La ligne tapée dans la fenêtre d’exécution nous renvoie =SUM(B2:G2)
Formule qu’il suffit maintenant de placer comme valeur alphanumérique dans la propriété Formula

Range("H2").Formula = "=SUM(B2:G2)"

Comment écrire sa formule dans une plage de cellules ?

Par exemple pour la plage H2:H101 de la feuille [Feuil1], il suffit tout simplement d’écrire.

With ThisWorkbook.Worksheets("Feuil1")
.Range("H2:H101").Formula = "=SUM(B2:G2)"
End With

La suite dans ce billet A l’aide de VBA, comment écrire une formule plus complexe dans une cellule ?

8 Comments

Hosting Deutschland on 30 juin 2016 at 0 h 13 min

D’autres utilisateurs de votre classeur peuvent ne pas comprendre vos formules. Dans la pratique, les formules matricielles n’etant generalement pas expliquees, si d’autres personnes sont appelees a modifier vos classeurs, vous devez soit eviter les formules matricielles, soit veiller a ce que ces personnes connaissent les formules matricielles et sachent comment les modifier en cas de besoin.

Philippe Tulliez on 12 juillet 2016 at 14 h 37 min

Bonjour,
Merci pour votre commentaire.
Toutefois et sauf erreur de ma part dans aucun de mes billets je n’ai utilisé de formule matricielle (au sens excel du terme en validant par Ctrl+Shift(Maj)+Enter(Retour) ).
De plus votre commentaire se trouve à la suite d’une explication de « Comment traduire une formule dans une plage de cellules en VBA« ) où l’exemple donné est fait sur la fonction SOMME qui est une des fonctions de base d’excel.
Philippe Tulliez
Reply

Attia on 9 mai 2022 at 22 h 27 min

Merci
C’est génial votre utilisation
De la fenêtre exécution
Je ne connaissait pas du tout
Un grand merci

C0ron@ on 23 octobre 2022 at 18 h 06 min

Bonjour Attia,
Merci pour votre commentaire.
La fenêtre d’exécution offre beaucoup d’avantage ^pour l’écriture du programme
Philippe
Reply

Gérard62 on 1 février 2023 at 17 h 05 min

Bonjour,
J’ai testé avec votre formule
.Range(« H2:H101 »).Formula = « RECHERCHEV($G2;RE!$A:$C;3;FAUX) » et j’ai une erreur 1004.
C’est dommage car c’est fort pratique.
Avez-vous une solution ?
Merci

C0ron@ on 2 février 2023 at 8 h 10 min

Bonjour,
Merci pour votre message.
Une erreur 1004 peut avoir plusieurs explications mais je vois déjà un souci. La formule doit être en anglais, je l’explique d’ailleurs dans mon article.
Remplacez RECHERCHEV par VLOOKUP et FAUX par FALSE
Reply
- Patrick Schellynck on 4 mars 2023 at 14 h 28 min
  
  J’ai la même erreur avec la formule FILTER, que ce soit en français ou en anglais
  Reply
  - Philippe Tulliez on 20 mars 2024 at 14 h 01 min
    
    Bonjour,
    Sans une vue de la ligne de code que vous avez utilisé, je ne peux vous répondre avec pertinence.
    Mettez la formule dans une cellule quelconque par exemple A1
    Exemple de la formule : =FILTRE(t_Tutoriel[URL];t_Tutoriel[Auteur]= »Philippe Tulliez »)
    Ensuite comme expliqué dans mon billet, tapez cette instruction dans la fenêtre d’exécution ? Range(« A1 »).Formula et confirmez par la touche Enter
    Je reçois comme réponse =FILTER(t_Tutoriel[URL],t_Tutoriel[Auteur]= »Philippe Tulliez »)
    On copie ce résultat que l’on colle entre les guillemets dans la propriété Formula du code VBA
    Par exemple : ThisWorkbook.Worksheets(« Accueil »).Range(« A1 »).Formula = « =FILTER(t_Tutoriel[URL],t_Tutoriel[Auteur]= » »Philippe Tulliez » ») »
    Reply