Bonjour,
Serait-ce trop vous demander d'adapter le système du Replace à une de mes formules ?
Non, je ne peux pas l'adapter mais je vais redonner une explication complète avec un exemple qui j'en suis convaincu te permettra de faire les adaptations.
Pour l'exemple, je pars du scénario suivant, une formule de recherche avec la fonction RECHERCHEV d'une liste de données se trouvant dans une feuille nommée [db] plage et qui renvoie le message "Pas trouvé" en cas d'insuccès.
La formule sera placée en cellule C2 de la feuille active et ensuite sur la plage de cellule C2:C101 de la même feuille
La formule est
=SIERREUR(RECHERCHEV($B2;db!$A$2:$J$106;EQUIV("Nom";db!$A$1:$J$1;0);FAUX);"Pas trouvé")
1) Pour connaître la syntaxe d'une formule à placer dans une plage de cellules d'excel par une procédure VBA, le plus simple est de l'écrire manuellement dans excel pour vérifier qu'elle fonctionne correctement.
2) Ensuite dans l'éditeur de VBA (VBE), aller dans la fenêtre d'exécution (que l'on active par le raccourci clavier Ctrl+G) et taper cette ligne de code (pour l'exemple la formule se trouve dans la cellule C2 de la feuille active
La formule affichée est
=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH("Nom",db!$A$1:$J$1,0),FALSE),"Pas trouvé")
3) Cette formule est une chaîne de caractères qui doit être entrée en VBA, placée entre les guillemets et s'il y a des guillemets présents dans la formule, ils doivent être doublé (dans l'exemple "Nom" et "pas trouvé"
Soit
ActiveSheet.Range("C2").Formula = "=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH(""Nom"",db!$A$1:$J$1,0),FALSE),""Pas trouvé"")"
Si la formule doit être répétée sur plusieurs cellules (ici dans l'exemple C2:C102)
La ligne d'instruction sera donc
ActiveSheet.Range("C2:C101").Formula = "=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH(""Nom"",db!$A$1:$J$1,0),FALSE),""Pas trouvé"")"
Ce qui est expliqué plus haut est un cas simple. On connaît le nombre de lignes et de colonnes de la plage.
Maintenant voici comment je procède si je veux rendre dynamique une formule dont on ne connaît pas la taille de la plage des données
Je place la formule dans une variable ou une constante de type String où l'on placera entre balises les inconnues. (Ici deux inconnues la taille de la plage des données et sa première ligne contenant les étiquettes de colonne.
La procédure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| Sub TestFormula()
Dim areaSearch As Range, areaSearch_Label As Range
Dim myFormula As String
' Affectation de la variable myFormula avec deux balises <TableauRecherche> et <Label>
myFormula = "=IFERROR(VLOOKUP($B2,<TableauRecherche>,MATCH(""Nom"",<Label>,0),FALSE),""Pas trouvé"")"
With ThisWorkbook
Set areaSearch = .Worksheets("db").Range("A1").CurrentRegion ' Equivalent de Ctrl+"A"
Set areaSearch_Label = areaSearch.Resize(1) ' Ligne des étiquettes de colonnes
End With
' Remplacement de la première balise par l'adresse complète de la plage des données
myFormula = Replace(myFormula, "<TableauRecherche>", areaSearch.Address(external:=True))
' Remplacement de la deuxième balise par l'adresse complète de la plage des étiquettes de colonnes
myFormula = Replace(myFormula, "<Label>", areaSearch_Label.Address(external:=True))
'
' Ce qui donnera la chaîne suivante : =IFERROR(VLOOKUP($B2,[DataBase1]db!$A$1:$J$106,MATCH("Nom",[DataBase1]db!$A$1:$J$1,0),FALSE),"Pas trouvé")
'
ActiveSheet.Range("C2:C101").Formula = myFormula ' Ecriture de la formule sur la plage C2:C101
End Sub |
Il est évident que ActiveSheet est utilisé juste pour l'exemple et qu'il faut donner le vrai nom de la feuille ainsi que le classeur à auquel elle est rattachée et idéalement son CodeName
Exemple de la fonction Replace
MsgBox Replace("Je m'appelle <Prenom>", "<Prenom>", "Philippe")
donnera Je m'appelle Philippe
Fonctions Excel en VBA
Répondre avec citation
et si celle-ci est pertinente pensez à voter 
) pour laquelle j'ai ouvert une autre discussion. Il n'est indiqué nulle part que vous êtes mon professeur attitré et gratuit, je ne veux pas vous déranger à chaque souci.
Partager