Utilisation de la fonction Rechercher et Remplacer dans Excel VBA
In this Article
Ce tutoriel montre comment utiliser les méthodes Rechercher et Remplacer dans Excel VBA.
Recherche VBA
Excel dispose d’excellents outils intégrés de Recherche et de Remplacement.
Ils peuvent être activés à l’aide des raccourcis CTRL + F (Rechercher) ou CTRL + H (Remplacer) ou via le ruban : Accueil > Édition > Rechercher et sélectionner.
En cliquant sur Options, vous pouvez voir les options de recherche avancée :
Vous pouvez facilement accéder aux méthodes Rechercher et Remplacer en utilisant VBA. Ces méthodes intégrées sont bien plus rapides que tout ce que vous pourriez écrire vous-même en VBA.
Exemple de Recherche en VBA
Pour démontrer la fonctionnalité de recherche, nous avons créé l’ensemble de données suivant dans Feuil1.
Si vous souhaitez suivre l’exemple, entrez les données dans votre propre classeur.
<<<<<<<<<<<<<<<<<<<<download??????>>>>>>>>>>>>>>>>>>>>
Recherche VBA sans Paramètres Facultatifs
Lorsque vous utilisez la méthode VBA Find, vous pouvez définir de nombreux paramètres facultatifs.
Nous vous recommandons vivement de définir tous les paramètres lorsque vous utilisez la méthode Find!
Si vous ne définissez pas les paramètres facultatifs, VBA utilisera les paramètres actuellement sélectionnés dans la fenêtre Rechercher d’Excel. Cela signifie que vous pouvez ne pas savoir quels paramètres de recherche sont utilisés lors de l’exécution du code. La recherche peut être exécutée sur l’ensemble du classeur ou sur une feuille. Il peut rechercher des formules ou des valeurs. Il n’y a aucun moyen de le savoir, sauf si vous vérifiez manuellement ce qui est actuellement sélectionné dans la fenêtre de recherche d’Excel.
Par souci de simplicité, nous allons commencer par un exemple sans paramètres optionnels définis.
Exemple de Recherche Simple
Examinons un exemple de recherche simple :
Sub TestRecherche()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Employés")
MsgBox MaPlage.Address
MsgBox MaPlage.Column
MsgBox MaPlage.Row
End SubCe code recherche le mot « Employés » dans la plage utilisée de Feuil1. S’il trouve « employés », il affecte la première plage trouvée à la variable de type Range MaPlage.
Ensuite, des boîtes de message s’affichent avec l’adresse, la colonne et la ligne du texte trouvé.
Dans cet exemple, les paramètres de recherche par défaut sont utilisés (en supposant qu’ils n’ont pas été modifiés dans la fenêtre de recherche d’Excel) :
- Le texte de la recherche correspond partiellement à la valeur de la cellule (une correspondance exacte n’est pas requise)
- La recherche n’est pas sensible à la casse.
- La recherche ne porte que sur une seule feuille de calcul
Ces paramètres peuvent être modifiés à l’aide de divers paramètres optionnels (voir ci-dessous).
Remarques sur la Méthode de Recherche
- La recherche ne sélectionne pas la cellule dans laquelle le texte est trouvé. Elle identifie uniquement la plage trouvée, que vous pouvez manipuler dans votre code.
- La méthode Find ne localisera que la première instance trouvée.
- Vous pouvez utiliser des caractères de remplacement (*), par exemple, rechercher ‘E*’
Rien Trouvé
Si le texte de recherche n’existe pas, l’objet plage restera vide. Cela pose un problème majeur lorsque votre code tente d’afficher les valeurs de l’emplacement parce qu’elles n’existent pas. Il en résultera un message d’erreur que vous ne souhaitez pas.
Heureusement, vous pouvez tester la présence d’un objet de plage vide dans VBA à l’aide de l’opérateur Is:
If Not MaPlage Is Nothing ThenAjoutez le code à notre exemple précédent :
Sub TestRecherche()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("employés")
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
MsgBox MaPlage.Column
MsgBox MaPlage.Row
Else
MsgBox "Introuvable"
End If
End Sub
Paramètres de Recherche
Jusqu’à présent, nous n’avons examiné qu’un exemple de base de l’utilisation de la méthode Find. Cependant, il existe un certain nombre de paramètres facultatifs disponibles pour vous aider à affiner votre recherche
| Paramètre | Type | Description | Valeurs |
| What | Requis | La valeur à rechercher | Tout type de données, tel qu’une chaîne de caractères ou un nombre |
| After | Facultatif | Référence de cellule unique pour commencer votre recherche | Adresse d’une cellule |
| LookIn | Facultatif | Utilisez les formules, les valeurs et les commentaires pour la recherche | xlValues, xlFormulas, xlComments |
| LookAt | Facultatif | Correspond à une partie ou à la totalité d’une cellule | xlWhole, xlPart |
| SearchOrder | Facultatif | Ordre de recherche (lignes ou colonnes) | xlByRows, xlByColummns |
| SearchDirection | Facultatif | Direction de la recherche (avant ou arrière) | xlNext, xlPrevious |
| MatchCase | Facultatif | La recherche est sensible à la casse ou non | Vrai ou Faux |
| MatchByte | Facultatif | Utilisé uniquement si vous avez installé la prise en charge des langues à deux octets, par exemple la langue chinoise | Vrai ou Faux |
| SearchFormat | Facultatif | Permet la recherche par format de cellule | Vrai ou Faux |
Paramètre After et Recherche de Valeurs Multiples
Le paramètre After permet de spécifier la cellule de départ de votre recherche. Cela s’avère utile lorsqu’il existe plus d’une instance de la valeur que vous recherchez.
Si une recherche a déjà trouvé une valeur et que vous savez que d’autres valeurs seront trouvées, vous utilisez la méthode Find avec le paramètre ‘After’ pour enregistrer la première instance et utiliser ensuite cette cellule comme point de départ de la recherche suivante.
Vous pouvez utiliser cette méthode pour trouver plusieurs instances de votre texte de recherche :
Sub TestRechercheMultiple()
Dim MaPlage As Range, AnciennePlage As Range, StrTrouvées As String
'Recherche la première instance de la chaine "Chauffage et Éclairage"
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Chauffage et Éclairage")
'Si la méthode ne trouve rien, la procédure est arrêtée
If MaPlage Is Nothing Then Exit Sub
'Affiche la première adresse trouvée
MsgBox MaPlage.Address
'Fait une copie de l'objet Range correspondant à la première instance trouvée
Set AnciennePlage = MaPlage
'Ajoute l'adresse trouvée à la chaine en utilisant le caractère "|" comme délimiteur
StrTrouvées = StrTrouvées & "|" & MaPlage.Address
'Boucle à travers toute la plage de données pour trouver d'autres instances
Do
'Recherche la chaine "Chauffage et Éclairage" en utlisant l'adresse trouvée précédemment comme paramètre "After"
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Chauffage et Éclairage", After:=Range(AnciennePlage.Address))
'Si l'adresse trouvée est déjà dans la liste d'adresse trouvée, la boucle est quittée – Ceci évite une boucle infinie
If InStr(StrTrouvées, MaPlage.Address) Then Exit Do
'Affiche la dernière adresse trouvée
MsgBox MaPlage.Address
'Ajoute la dernière adresse trouvée à la chaine d'adresses trouvées
StrTrouvées = StrTrouvées & "|" & MaPlage.Address
'Fait une copie de la plage actuelle
Set AnciennePlage = MaPlage
Loop
End SubCe code va itérer à travers la plage de cellule utilisée (représentée facilement en VBA par l’objet UsedRange), et va afficher l’adresse chaque fois qu’il trouve une instance de ‘Chauffage et Éclairage’
Notez que le code continuera à s’exécuter en boucle jusqu’à ce qu’une adresse en double soit trouvée dans StrTrouvées, auquel cas il quittera la boucle Do.
Paramètre LookIn
Vous pouvez utiliser le paramètre LookIn pour spécifier l’élément de la cellule dans lequel vous souhaitez effectuer la recherche. Vous pouvez spécifier des valeurs, des formules ou des commentaires dans une cellule.
- xlValues – Recherche les valeurs des cellules (la valeur finale d’une cellule après son calcul)
- xlFormulas – Effectue une recherche dans la formule de la cellule elle-même (ce qui est entré dans la cellule)
- xlComments – Effectue des recherches dans les notes de cellule
- xlCommentsThreaded – Effectue des recherches dans les commentaires des cellules
En supposant qu’une formule ait été saisie sur la feuille de calcul, vous pouvez utiliser cet exemple de code pour trouver le premier emplacement de toute formule :
Sub TestRechercheFormules()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("=", LookIn:=xlFormulas)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
End Sub
Si le paramètre ‘LookIn’ était défini à xlValues, le code afficherait un message ‘Introuvable’. Dans cet exemple, il renvoie B10.
Utilisation du Paramètre LookAt
Le paramètre LookAt détermine si la recherche doit porter sur une correspondance exacte entre les cellules ou sur toute cellule contenant la valeur recherchée.
- xlWhole – La cellule entière doit correspondre à la valeur recherchée
- xlPart – Effectue une recherche dans une cellule pour la chaîne de recherche
Cet exemple de code va localiser la première cellule contenant le texte « Éclairage ». Avec Lookat:=xlPart, il renvoie une correspondance pour « Chauffage et Éclairage ».
Sub TestLookAt()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Éclairage", Lookat:=xlPart)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
End Sub
Si xlWhole était défini, une correspondance ne serait retournée que si la valeur de la cellule était « Éclairage ».
Paramètre SearchOrder
Le paramètre SearchOrder indique comment la recherche sera effectuée dans la plage.
- xlRows – La recherche est effectuée ligne par ligne
- xlColumns – La recherche est effectuée colonne par colonne
Sub TestSearchOrder()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("employés", SearchOrder:=xlColumns)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
End SubCela influence la correspondance qui sera trouvée en premier.
En utilisant les données de test entrées dans la feuille de calcul précédemment, lorsque l’ordre de recherche est xlColumns, la cellule trouvée est A5. Lorsque le paramètre d’ordre de recherche est modifié en xlRows, la cellule trouvée est C4
Ceci est important si vous avez des valeurs en double dans la plage de recherche et que vous voulez trouver la première instance sous un nom de colonne particulier.
Paramètre SearchDirection
Le paramètre SearchDirection indique la direction dans laquelle la recherche sera effectuée – en fait, vers l’avant ou vers l’arrière.
- xlNext – Recherche de la prochaine valeur correspondante dans la plage
- xlPrevious – Recherche de la valeur correspondante précédente dans la plage
Là encore, s’il y a des valeurs en double dans la plage de recherche, cela peut avoir un effet sur celle qui sera trouvée en premier.
Sub TestSearchDirection()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Chauffage", SearchDirection:=xlPrevious)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
End Sub
En utilisant ce code sur les données de test, une direction de recherche de xlPrevious renverra un emplacement de C9. L’utilisation du paramètre xlNext renvoie l’emplacement A4.
Le paramètre Next signifie que la recherche commence dans le coin supérieur gauche de la plage de recherche et se poursuit vers le bas. Le paramètre Previous signifie que la recherche commence dans le coin inférieur droit de la plage de recherche et se poursuit vers le haut.
Paramètre MatchByte
Le paramètre MatchBye est uniquement utilisé pour les langues qui utilisent un double octet pour représenter chaque caractère, comme le chinois, le russe et le japonais.
Si ce paramètre a la valeur ‘True’, Find ne fera correspondre que les caractères à double octet avec les caractères à double octet. Si ce paramètre a la valeur ‘False’, un caractère à double octet sera associé à des caractères à simple ou double octet.
Paramètre SearchFormat
Le paramètre SearchFormat vous permet de rechercher les formats de cellule correspondants. Il peut s’agir d’une police particulière utilisée, d’une police en gras ou d’une couleur de texte. Avant d’utiliser ce paramètre, vous devez définir le format requis pour la recherche à l’aide de la propriété Application.FindFormat.
Voici un exemple d’utilisation :
Sub TestSearchFormat()
Dim MaPlage As Range
Application.FindFormat.Clear
Application.FindFormat.Font.Bold = True
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Chauffage", Searchformat:=True)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
Application.FindFormat.Clear
End Sub
Dans cet exemple, la propriété FindFormat est définie pour rechercher une police en gras. L’instruction Find recherche ensuite le mot ‘heat’ en définissant le paramètre SearchFormat à True afin de renvoyer une instance de ce texte que si la police est en gras.
Dans l’exemple de feuille de calcul présenté plus haut, cette opération renvoie A9, qui est la seule cellule contenant le mot « Chauffage » en caractères gras.
Assurez-vous que la propriété FindFormat est effacée à la fin du code. Si vous ne le faites pas, votre prochaine recherche en tiendra compte et renverra des résultats incorrects.
Lorsque vous utilisez un paramètre SearchFormat, vous pouvez également utiliser un caractère générique (*) comme valeur de recherche. Dans ce cas, la recherche portera sur toute valeur comportant une police en gras :
Set MaPlage = Sheets("Feuil1").UsedRange.Find("*", Searchformat:=True)Utilisation de Plusieurs Paramètres
Tous les paramètres de recherche présentés ici peuvent être utilisés en combinaison les uns avec les autres si nécessaire.
Par exemple, vous pouvez combiner le paramètre « LookIn » avec le paramètre « MatchCase » de manière à examiner l’ensemble du texte de la cellule, tout en respectant la casse
Sub TestParamètresMultiples()
Dim MaPlage As Range
Set MaPlage = Sheets("Feuil1").UsedRange.Find("Chauffage et Éclairage", LookAt:=xlWhole, MatchCase:=True)
If Not MaPlage Is Nothing Then
MsgBox MaPlage.Address
Else
MsgBox "Introuvable"
End If
End Sub
Dans cet exemple, le code renvoie A4, mais si nous n’utilisons qu’une partie du texte, par exemple ‘Chauffage’, rien ne sera trouvé, car la correspondance porte sur la totalité de la valeur de la cellule. De plus, l’opération échouerait parce que la casse ne correspond pas.
Set MaPlage = Sheets("Feuil1").UsedRange.Find("chauffage", LookAt:=xlWhole, MatchCase:=True)Remplacer dans Excel VBA
Il existe, comme vous vous en doutez, une fonction de remplacement dans Excel VBA, qui fonctionne de manière très similaire à la fonction de recherche, mais qui remplace les valeurs à l’emplacement de la cellule trouvée par une nouvelle valeur.
Voici les paramètres que vous pouvez utiliser dans une instruction de méthode Replace. Ils fonctionnent exactement de la même manière que pour l’instruction de méthode Find. La seule différence avec la méthode « Find » est que vous devez spécifier un paramètre de remplacement.
| Nom | Type | Description | Valeurs |
| What | Requis | La valeur à rechercher | Tout type de données, tel qu’une chaîne de caractères ou un nombre |
| Replacement | Requis | La chaîne de remplacement. | Tout type de données, tel qu’une chaîne ou un chiffre |
| LookAt | Facultatif | Correspond à une partie ou à la totalité d’une cellule | xlPart ou xlWhole |
| SearchOrder | Facultatif | L’ordre dans lequel la recherche doit être effectuée – Lignes ou Colonnes | xlByRows ou xlByColumns |
| MatchCase | Facultatif | La recherche est sensible à la casse ou non | Vrai ou Faux |
| MatchByte | Facultatif | Utilisé uniquement si vous avez installé la prise en charge des langues à double octet | Vrai ou Faux |
| SearchFormat | Facultatif | Permet la recherche par format de cellule | Vrai ou Faux |
| ReplaceFormat | Facultatif | Le format de remplacement de la méthode. | Vrai ou Faux |
Le paramètre Replace Format recherche une cellule avec un format particulier, par exemple en gras, de la même manière que le paramètre SearchFormat dans la méthode Find. Vous devez d’abord définir la propriété Application.FindFormat, comme le montre le code de l’exemple Find présenté précédemment
Remplacement sans Paramètres Facultatifs
Dans sa forme la plus simple, il vous suffit de spécifier ce que vous recherchez et ce par quoi vous voulez le remplacer.
Sub TestReplace()
Sheets("Feuil1").UsedRange.Replace What:="Chauffage et Éclairage", Replacement:="C & É"
End SubNotez que la méthode Find ne renvoie que la première instance de la valeur trouvée, alors que la méthode Replace parcourt toute la plage spécifiée et remplace tout ce qui correspond.
Le code de remplacement présenté ici remplacera toutes les occurrences de « Chauffage et Éclairage » par « C & É » dans toute la plage de cellules définie par l’objet UsedRange
Utilisation de VBA pour Rechercher ou Remplacer du Texte dans une Chaîne de Texte VBA
Les exemples ci-dessus fonctionnent parfaitement lorsque vous utilisez VBA pour interagir avec des données Excel. Cependant, pour interagir avec les chaînes de caractères VBA, vous pouvez utiliser les fonctions VBA intégrées telles que INSTR et REPLACE.
Vous pouvez utiliser la fonction Fonction INSTR pour localiser une chaîne de texte à l’intérieur une chaîne plus longue.
Sub TestInstr()
MsgBox InStr("Ceci est la chaîne MonTexte", "MonTexte")
End SubCet exemple de code renvoie la valeur 20, qui correspond à la position numérique où se trouve ‘MonTexte’ dans la chaîne à rechercher.
Notez que la recherche est sensible à la casse. Si ‘MonTexte’ est tout en minuscules, la valeur 0 sera renvoyée, ce qui signifie que la chaîne de recherche n’a pas été trouvée. Nous verrons ci-dessous comment désactiver la sensibilité à la casse.
INSTR – Start
Deux autres paramètres facultatifs sont disponibles. Vous pouvez spécifier le point de départ de la recherche :
MsgBox InStr(9, "Ceci est la chaîne de MonTexte", "MonTexte")Le point de départ est spécifié comme étant 9, donc la recherche retournera toujours 20. Si le point de départ était 21, la recherche renverrait 0 (aucune correspondance) car le point de départ serait trop avancé.
INSTR – Sensibilité à la casse
Vous pouvez également définir un paramètre de comparaison sur vbBinaryCompare ou vbTextCompare. Si vous définissez ce paramètre, l’instruction doit avoir une valeur de paramètre de départ.
- vbBinaryCompare – Sensible à la casse (par défaut)
- vbTextCompare – Non sensible à la casse
MsgBox InStr(1, "Ceci est la chaîne MonTexte", "montexte", vbTextCompare)Cette instruction renvoie toujours 20, même si le texte recherché est en minuscules.
Pour désactiver la sensibilité à la casse, vous pouvez également déclarer l’option Compare Text en haut de votre module de code.
Fonction VBA Replace
Si vous souhaitez remplacer les caractères d’une chaîne par un texte différent dans votre code, la méthode Replace est idéale pour cela :
Sub TestReplaceStr()
MsgBox Replace("Ceci est la chaîne MonTexte", "MonTexte", "Mon Texte")
End SubCe code remplace ‘MonTexte’ par ‘Mon Texte’. Notez que la chaîne de recherche est sensible à la casse car une comparaison binaire est utilisée par défaut.
Vous pouvez également ajouter d’autres paramètres facultatifs:
- Start – définit la position dans la chaîne initiale à partir de laquelle le remplacement doit commencer. Contrairement à la méthode Find, elle renvoie une chaîne tronquée à partir du numéro de caractère défini par le paramètre Start.
- Count – définit le nombre de remplacements à effectuer. Par défaut, Replace modifiera chaque instance du texte recherché trouvée, mais vous pouvez limiter cette opération à un seul remplacement en définissant le paramètre Count à 1
- Compare – comme dans la méthode Find, vous pouvez spécifier une recherche binaire ou une recherche de texte en utilisant vbBinaryCompare ou vbTextCompare. La recherche binaire est sensible à la casse et la recherche textuelle n’est pas sensible à la casse
MsgBox Replace("Ceci est la chaine MonTexte (montexte)", "MonTexte", "Mon Texte", 9, 1, vbTextCompare)Ce code renvoie ‘la chaine Mon Texte (montexte)’. En effet, le point de départ donné est 9, et la nouvelle chaîne retournée commence donc au caractère 9. Seul le premier ‘MonTexte’ a été modifié, car le paramètre Count est défini sur 1.
La méthode Replace est idéale pour résoudre des problèmes tels que les noms de personnes contenant des apostrophes, par exemple O’Flynn. Si vous utilisez des guillemets simples pour définir une valeur de chaîne de caractères et qu’il y a une apostrophe, cela provoquera une erreur car le code interprétera l’apostrophe comme la fin de la chaîne et ne reconnaîtra pas le reste de la chaîne.
Vous pouvez utiliser la méthode Replace pour remplacer l’apostrophe par rien, en la supprimant complètement.
