Ce didacticiel montrera comment utiliser les méthodes Rechercher et remplacer dans Excel VBA.
Rechercher VBA
Excel a un excellent intégré Trouve et Rechercher et remplacer outils.
Ils peuvent être activés avec les raccourcis CTRL + F (Rechercher) ou CTRL + H (Remplacer) ou via le Ruban : Accueil > Édition > Rechercher et sélectionner.
En cliquant Options, vous pouvez voir les options de recherche avancée :
Vous pouvez facilement accéder aux méthodes Rechercher et Remplacer à l'aide de VBA. Ces méthodes intégrées sont beaucoup plus rapides que tout ce que vous pourriez écrire vous-même en VBA.
Trouver un exemple VBA
Pour illustrer la fonctionnalité Rechercher, nous avons créé l'ensemble de données suivant dans Sheet1.
Si vous souhaitez suivre, entrez les données dans votre propre classeur.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
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 fortement de définir tous les paramètres lors de l'utilisation de 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 ne savez peut-être pas quels paramètres de recherche sont utilisés lorsque le code est exécuté. La recherche peut être exécutée sur l'ensemble du classeur ou sur une feuille. Il pourrait 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.
Pour plus de simplicité, nous allons commencer par un exemple sans paramètres facultatifs définis.
Exemple de recherche simple
Regardons un exemple de recherche simple :
| 123456789 | Sous-testRechercher()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("employee")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowFin du sous-marin |
Ce code recherche « employé » dans la plage utilisée de Sheet1. S'il trouve « employé », il affectera la première plage trouvée à la variable de plage MyRange.
Ensuite, des boîtes de message s'afficheront 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 recherche correspond partiellement à la valeur de la cellule (une correspondance exacte de la cellule n'est pas requise)
- La recherche n'est pas sensible à la casse.
- Rechercher uniquement dans une seule feuille de calcul
Ces réglages peuvent être modifiés avec divers paramètres facultatifs (discutés ci-dessous).
Trouver des notes de méthode
- Rechercher ne sélectionne pas la cellule où se trouve le texte. Il identifie uniquement la plage trouvée, que vous pouvez manipuler dans votre code.
- La méthode Find localisera uniquement la première instance trouvée.
- Vous pouvez utiliser des caractères génériques (*), par ex. rechercher « E* »
rien n'a été trouvé
Si le texte de recherche n'existe pas, l'objet de plage restera vide. Cela pose un problème majeur lorsque votre code essaie d'afficher les valeurs d'emplacement car elles n'existent pas. Cela entraînera un message d'erreur que vous ne voulez pas.
Heureusement, vous pouvez tester un objet de plage vide dans VBA à l'aide de l'opérateur Is :
| 1 | Si ce n'est pas MyRange n'est rien alors |
Ajout du code à notre exemple précédent :
| 12345678910111213 | Sous-testRechercher()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("employee")Si ce n'est pas MyRange n'est rien alorsMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
Rechercher des paramètres
Jusqu'à présent, nous n'avons examiné qu'un exemple de base d'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 | Taper | La description | Valeurs |
| Quoi | Obligatoire | La valeur à rechercher | Tout type de données tel qu'une chaîne ou un nombre |
| Après | Optionnel | Référence de cellule unique pour commencer votre recherche | Adresse de la cellule |
| Regarder dans | Optionnel | Utilisez des formules, des valeurs, des commentaires pour la recherche | xlValues, xlFormules, xlCommentaires |
| Regarder | Optionnel | Faire correspondre une partie ou la totalité d'une cellule | xlEntier, xlPartie |
| Ordre de recherche | Optionnel | L'ordre de recherche dans - lignes ou colonnes | xlByRows, xlByColummns |
| RechercheDirection | Optionnel | Direction de recherche pour entrer - en avant ou en arrière | xlSuivant, xlPrécédent |
| Cas de correspondance | Optionnel | La recherche est sensible à la casse ou non | Vrai ou faux |
| Octet de correspondance | Optionnel | Utilisé uniquement si vous avez installé la prise en charge des langues à double octet, par ex. langue chinoise | Vrai ou faux |
| Format de recherche | Optionnel | Autoriser la recherche par format de cellule | Vrai ou faux |
Après le paramètre et rechercher plusieurs valeurs
Vous utilisez le Après paramètre pour spécifier la cellule de départ de votre recherche. Ceci est utile lorsqu'il existe plusieurs instances de la valeur que vous recherchez.
Si une recherche a déjà trouvé une valeur et que vous savez qu'il y aura d'autres valeurs trouvées, vous utilisez la méthode Find avec le paramètre 'After' pour enregistrer la première instance, puis utilisez cette cellule comme point de départ pour la prochaine recherche.
Vous pouvez l'utiliser pour trouver plusieurs instances de votre texte de recherche :
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds()Dim MyRange As Range, OldRange As Range, FindStr As String'Recherchez la première instance de "'Light & Heat"Définir MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat")'Si introuvable, quittezSi MyRange n'est rien, quittez Sub'Afficher la première adresse trouvéeMsgBox MyRange.Address'Faire une copie de l'objet de plageDéfinir OldRange = MyRange'Ajoute l'adresse à la chaîne délimitée par un "|" personnageFindStr = FindStr & "|" & MaPlage.Adresse'Itérer à travers la plage à la recherche d'autres instancesFaire'Rechercher 'Light & Heat' en utilisant l'adresse trouvée précédente comme paramètre AprèsSet MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))'Si l'adresse a déjà été trouvée, quittez la boucle do - cela arrête la boucle continueSi InStr(FindStr, MyRange.Address) Alors Exit Do'Afficher la dernière adresse trouvéeMsgBox MyRange.Address'Ajouter la dernière adresse à la chaîne d'adressesFindStr = FindStr & "|" & MaPlage.Adresse'faire une copie de la plage actuelleDéfinir OldRange = MyRangeBoucleFin du sous-marin |
Ce code parcourra la plage utilisée et affichera l'adresse chaque fois qu'il trouvera une instance de « Light & Heat »
Notez que le code continuera à boucler jusqu'à ce qu'une adresse en double soit trouvée dans FindStr, auquel cas il quittera la boucle Do.
Paramètre de recherche
Vous pouvez utiliser le Paramètre de recherche pour spécifier le composant de la cellule dans lequel vous souhaitez effectuer la recherche. Vous pouvez spécifier des valeurs, des formules ou des commentaires dans une cellule.
- xlValeurs - Recherche les valeurs des cellules (la valeur finale d'une cellule après son calcul)
- xlFormules - Recherches dans la formule de la cellule elle-même (tout ce qui est entré dans la cellule)
- xlCommentaires - Recherches dans les notes de cellule
- xlCommentaires Fileté - Recherches dans les commentaires des cellules
En supposant qu'une formule a été entrée dans la feuille de calcul, vous pouvez utiliser cet exemple de code pour trouver le premier emplacement de n'importe quelle formule :
| 12345678910 | Sous-testLookIn()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("=", LookIn:=xlFormulas)Si ce n'est pas MyRange n'est rien alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
Si le paramètre « LookIn » était défini sur xlValues, le code afficherait un message « Not Found ». Dans cet exemple, il renverra B10.
Utilisation du paramètre LookAt
Les Paramètre LookAt détermine si find recherchera une correspondance de cellule exacte ou toute cellule contenant la valeur de recherche.
- xlEntier - Nécessite que la cellule entière corresponde à la valeur de recherche
- xlPart - Recherches dans une cellule pour la chaîne de recherche
Cet exemple de code localisera la première cellule contenant le texte « light ». Avec Regardez :=xlPart, il renverra un match pour "Light & Heat".
| 123456789 | Sous-testLookAt()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart)Si ce n'est pas MyRange n'est rien, alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
Si xlEntier a été défini, une correspondance ne serait renvoyée que si la valeur de la cellule était « légère ».
Paramètre SearchOrder
Les Paramètre SearchOrder dicte comment la recherche sera effectuée dans toute la plage.
- xlRows - La recherche se fait ligne par ligne
- xlXolumnes - La recherche se fait colonne par colonne
| 123456789 | Ordre de recherche de sous-test()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("employee", SearchOrder:=xlColumns)Si ce n'est pas MyRange n'est rien alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
Cela influence quelle correspondance sera trouvée en premier.
En utilisant les données de test saisies précédemment dans la feuille de calcul, lorsque l'ordre de recherche est des colonnes, la cellule localisée est A5. Lorsque le paramètre d'ordre de recherche est modifié en xlRows, la cellule localisée est C4
Ceci est important si vous avez des valeurs en double dans la plage de recherche et que vous souhaitez trouver la première instance sous un nom de colonne particulier.
Paramètre SearchDirection
Les Paramètre SearchDirection dicte dans quelle direction la recherche ira - effectivement vers l'avant ou vers l'arrière.
- xlSuivant - Recherche de la prochaine valeur correspondante dans la plage
- xlPrécédent - Recherche de la valeur correspondante précédente dans la plage
Encore une fois, s'il y a des valeurs en double dans la plage de recherche, cela peut avoir un effet sur celle qui est trouvée en premier.
| 12345678910 | Sous TestRechercheDirection()Dim MyRange As RangeDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("heat", SearchDirection:=xlPrevious)Si ce n'est pas MyRange n'est rien alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
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 renverra un emplacement A4.
Le paramètre Next signifie que la recherche commencera dans le coin supérieur gauche de la plage de recherche et se déroulera vers le bas. Le paramètre Précédent signifie que la recherche commencera dans le coin inférieur droit de la plage de recherche et progressera vers le haut.
Paramètre MatchByte
Les Paramètre MatchBye n'est utilisé que 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 est défini sur « True », alors Find correspondra uniquement aux caractères à deux octets avec des caractères à deux octets. Si le paramètre est défini sur « False », un caractère à deux octets correspondra aux caractères à un ou deux octets.
Paramètre SearchFormat
Les Paramètre SearchFormat vous permet de rechercher des 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 :
| 12345678910111213 | Sous-testFormatRecherche()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueDéfinir MyRange = Sheets("Sheet1").UsedRange.Find("heat", Searchformat:=True)Si ce n'est pas MyRange n'est rien alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siApplication.FindFormat.ClearFin du sous-marin |
Dans cet exemple, le RechercherFormat La propriété est définie pour rechercher une police en gras. L'instruction Find recherche ensuite le mot « heat » en définissant le paramètre SearchFormat sur True afin qu'il ne renvoie une instance de ce texte que si la police est en gras.
Dans l'exemple de données de feuille de calcul présenté précédemment, cela renverra A9, qui est la seule cellule contenant le mot « chaleur » en caractères gras.
Assurez-vous que la propriété FindFormat est désactivée à la fin du code. Si vous ne le faites pas, votre prochaine recherche en tiendra toujours 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, il recherchera n'importe quelle valeur avec une police en gras :
| 1 | Définir MyRange = Sheets("Sheet1").UsedRange.Find("*", Searchformat:=True) |
Utilisation de plusieurs paramètres
Tous les paramètres de recherche discuté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 » afin que vous regardiez l'ensemble du texte de la cellule, mais il est sensible à la casse
| 123456789 | Sous-testParamètres multiples()Dim MyRange As RangeSet MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True)Si ce n'est pas MyRange n'est rien, alorsMsgBox MyRange.AddressAutreMsgBox "Non trouvé"Fin siFin du sous-marin |
Dans cet exemple, le code retournera A4, mais si nous n'utilisons qu'une partie du texte, par ex. « chaleur », rien ne serait trouvé car nous faisons correspondre sur l'ensemble de la valeur de la cellule. En outre, il échouerait en raison de la non-correspondance de la casse.
| 1 | Définir MyRange = Sheets("Sheet1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True) |
Remplacer dans Excel VBA
Il existe, comme vous pouvez vous y attendre, une fonction Remplacer dans Excel VBA, qui fonctionne de manière très similaire à "Rechercher", mais remplace les valeurs à l'emplacement de la cellule trouvée par une nouvelle valeur.
Ce sont les paramètres que vous pouvez utiliser dans une instruction de méthode Replace. Celles-ci fonctionnent exactement de la même manière que pour l'instruction de méthode Find. La seule différence avec « Rechercher » est que vous devez spécifier un paramètre de remplacement.
| Nom | Taper | La description | Valeurs |
| Quoi | Obligatoire | La valeur à rechercher | Tout type de données tel qu'une chaîne ou un nombre |
| Remplacement | Obligatoire | La chaîne de remplacement. | Tout type de données tel qu'une chaîne ou un nombre |
| Regarder | Optionnel | Faire correspondre une partie ou la totalité d'une cellule | xlPart ou xlWhole |
| Ordre de recherche | Optionnel | L'ordre de recherche dans - Lignes ou Colonnes | xlByRows ou xlByColumns |
| Cas de correspondance | Optionnel | La recherche est sensible à la casse ou non | Vrai ou faux |
| Octet de correspondance | Optionnel | Utilisé uniquement si vous avez installé la prise en charge de la langue à double octet | Vrai ou faux |
| Format de recherche | Optionnel | Autoriser la recherche par format de cellule | Vrai ou faux |
| RemplacerFormat | Optionnel | Le format de remplacement de la méthode. | Vrai ou faux |
Le paramètre Remplacer le format recherche une cellule avec un format particulier, par ex. bold de la même manière que le paramètre SearchFormat fonctionne dans la méthode Find. Vous devez d'abord définir la propriété Application.FindFormat, comme indiqué dans l'exemple de code Find présenté précédemment.
Remplacer sans paramètres facultatifs
Dans sa forme la plus simple, il vous suffit de spécifier ce que vous recherchez et par quoi vous souhaitez le remplacer.
| 123 | Sub TestReplace()Sheets("Sheet1").UsedRange.Replace What:="Light & Heat", Replacement:="L & H"Fin du sous-marin |
Notez que la méthode Find ne retournera que la première instance de la valeur correspondante, tandis que la méthode Replace fonctionne sur toute la plage spécifiée et remplace tout ce sur quoi elle trouve une correspondance.
Le code de remplacement affiché ici remplacera chaque instance de « Light & Heat » par « L & H » sur 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 très bien lorsque vous utilisez VBA pour interagir avec des données Excel. Cependant, pour interagir avec les chaînes VBA, vous pouvez utiliser des fonctions VBA intégrées telles que INSTR et REPLACE.
Vous pouvez utiliser le Fonction INSTR pour localiser une chaîne de texte dans une chaîne plus longue.
| 123 | Sous-TestInstr()MsgBox InStr("Ceci est la chaîne MonTexte", "MonTexte")Fin du sous-marin |
Cet exemple de code renverra la valeur de 9, qui est la position numérique où « MyText » se trouve dans la chaîne à rechercher.
Notez qu'il est sensible à la casse. Si 'MyText' est entièrement en minuscule, une valeur de 0 sera renvoyée, ce qui signifie que la chaîne de recherche n'a pas été trouvée. Ci-dessous, nous verrons comment désactiver la sensibilité à la casse.
INSTR - Démarrer
Deux autres paramètres facultatifs sont disponibles. Vous pouvez spécifier le point de départ de la recherche :
| 1 | MsgBox InStr(9, "Ceci est la chaîne MyText", "MyText") |
Le point de départ est spécifié comme 9 donc il retournera toujours 9. Si le point de départ était 10, alors il retournerait 0 (pas de correspondance) car le point de départ serait trop en avant.
INSTR - Sensibilité à la casse
Vous pouvez également définir un paramètre de comparaison sur vbBinaryComparer ou vbTextComparer. Si vous définissez ce paramètre, l'instruction doit avoir une valeur de paramètre de début.
- vbBinaryComparer - Sensible à la casse (par défaut)
- vbTextComparer - Pas sensible à la casse
| 1 | MsgBox InStr(1, "Ceci est la chaîne MyText", "mytext", vbTextCompare) |
Cette instruction renverra toujours 9, même si le texte recherché est en minuscule.
Pour désactiver la sensibilité à la casse, vous pouvez également déclarer Option Compare Text en haut de votre module de code.
Fonction de remplacement VBA
Si vous souhaitez remplacer des caractères dans une chaîne par un texte différent dans votre code, la méthode Replace est idéale pour cela :
| 123 | Sub TestReplace()MsgBox Replace("Ceci est la chaîne MonTexte", "MonTexte", "Mon Texte")Fin du sous-marin |
Ce code remplace « MyText » par « My Text ». Notez que la chaîne de recherche est sensible à la casse car une comparaison binaire est la valeur par défaut.
Vous pouvez également ajouter d'autres paramètres facultatifs :
- Début - 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.
- Compter - définit le nombre de remplacements à effectuer. Par défaut, Remplacer modifiera chaque instance du texte de recherche trouvé, mais vous pouvez limiter cela à un seul remplacement en définissant le paramètre Count sur 1
- Comparer - comme dans la méthode Find vous pouvez spécifier une recherche binaire ou une recherche textuelle en utilisant vbBinaryComparer ou vbTextComparer. Le binaire est sensible à la casse et le texte n'est pas sensible à la casse
| 1 | MsgBox Replace("Ceci est la chaîne MyText (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Ce code renvoie 'Ma chaîne de texte (montexte)'. C'est parce que le point de départ donné est 9, donc la nouvelle chaîne renvoyée commence au caractère 9. Seul le premier « MyText » 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 ex. O'Flynn. Si vous utilisez des guillemets simples pour définir une valeur de chaîne 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.
