Code VBA pour trouver des cellules dans une plage

La méthode Find de la classe Range est utilisée pour trouver les cellules contenant une valeur ou un format donné dans une plage.

Rechercher dans la feuille de calcul

Dans sa forme de base, Find est appelé comme le code ci-dessous, qui sélectionne la cellule trouvée. Le code ne fonctionnera que si la valeur est effectivement présente sur la feuille de calcul.


ActiveSheet.UsedRange.Find(What:="x").Select

Remarque : pour que l'exemple de code soit court, nous utilisons la méthode ActiveSheet pour fournir l'objet de feuille de calcul parent dont la classe Range est utilisée. Dans un code réel, il est préférable d'utiliser des variables d'objet explicitement déclarées et affectées, dans le cas présent : Dim wsActiveSheet As Worksheet: Set wsActiveSheet = ActiveSheet, très probablement avec une autre expression d'affectation d'objet.

L'objet de la classe Range est ici obtenu à partir de la procédure Worksheet UsedRange.

La procédure renvoie un objet de type Range qui est éventuellement affecté à une variable rngFound. En général, vous utiliserez une variable, ici : strWhat pour affecter le paramètre What.


Dim strWhat As String : strWhat = 
Dim rngFound As Range 
Set rngFound = ActiveSheet.UsedRange.Find(What:=strWhat)

La méthode Find renvoie un résultat Nothing si aucune correspondance n'est trouvée.

Note : La méthode Rechercher n'affecte pas la sélection ou la cellule active.

Trouver dans Range

Recherche d'une valeur dans une colonne

Le code ci-dessous vous permet de trouver une cellule contenant la valeur d'une colonne donnée.


Dim rngColumn As Range 
Set rngColumn = ActiveSheet.Columns(3) 
Dim rngFound As Range 
Set rngFound = rngColumn.Find(What:="x").Select

Recherche d'une valeur dans une ligne

Utilisez le code suivant pour trouver une cellule contenant la valeur d'une ligne.


Dim rngRow As Range 
Set rngRow = ActiveSheet.Rows(8) 
Dim rngFound As Range 
Set rngFound = rngRow.Find(What:="x")

Quoi : spécifier des critères de recherche

Find dialog options — Les paramètres de la méthode de recherche Excel correspondent en grande partie aux options de la boîte de dialogue Rechercher.

Trouver ce que

Les données à rechercher. Il peut s'agir d'une chaîne de caractères ou de n'importe quel type de données Microsoft Excel. Quelques conseils sur les valeurs de paramètres appropriées :

Pour rechercher des valeurs numériques, utilisez Lookin:=xlFormulas et LookAt:=xlWhole.
Pour rechercher des valeurs de date, utilisez Lookin:=xlFormulas et LookAt:=xlWhole. La date fournie doit être du type 25/02/2024, ce que vous pouvez obtenir en utilisant FormatDateTime(dtDate, vbGeneralDate).

Avertissement :

Lors de la recherche de valeurs numériques, LookAt:=xlPart peut vous amener à trouver plus de nombres que vous ne le souhaitez : si vous recherchez 1, vous obtiendrez également 301, 12, etc.
Si les colonnes sont plus petites, les cellules peuvent ne pas afficher la valeur, mais ### à la place. Il est peut-être plus sûr de combiner avecLookin:=xlFormulas

After : cellule de départ pour la recherche

Si After a été spécifié, la recherche commence après cette cellule ; la cellule spécifiée n'est pas recherchée avant que la méthode ne revienne à cette cellule.

Remarque : Si vous ne spécifiez pas cet argument, la recherche commence après la cellule située dans le coin supérieur gauche de la plage A1. Ceci est différent de l'utilisation de la boîte de dialogue Rechercher, qui commence à la cellule active.

Erreur : S'il est utilisé, l'argument After doit être une seule cellule de la plage. Si ce n'est pas le cas, vous obtenez l' erreur 13 : Type mismatch.

Le code ci-dessous lance la recherche après la cellule active en imitant la boîte de dialogue de recherche.


Dim rngFound As Range 
Set rngFound = ws.UsedRange.Find(What:="x", After:=ActiveCell)

LookIn : Formules, valeurs, notes, commentaires

Peut être l'une des constantes XlFindLookIn suivantes : xlFormulas (Formules), xlValues (Valeurs), xlComments (Notes), ou xlCommentsThreaded (Commentaires).


Dim rngFound As Range 
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookIn:=xlComments)

xlFormules vs xlValeurs

La différence entre la recherche de formules ou de valeurs peut être observée expérimentalement. Mettez dans A1 la valeur x et dans B1 la formule =A1. Dans la boîte de dialogue Rechercher, appuyez sur Find All. Si l'option Formules est sélectionnée, seul A1 est trouvé, alors que si l'option Valeurs est sélectionnée, A1 et B1 sont tous deux trouvés.

Conclusion :

Les formules prennent en compte ce que la cellule contient et ce qui est disponible dans la barre de formule;
Les valeurs correspondent à ce qui est affiché comme valeur dans la cellule de la grille.

Attention : Les paramètres de LookIn, LookAt et SearchOrder sont enregistrés chaque fois que vous utilisez cette méthode. Si vous ne spécifiez pas de valeurs pour ces arguments lors du prochain appel de la méthode, les valeurs enregistrées sont utilisées. La définition de ces arguments modifie les paramètres de la boîte de dialogue Rechercher, et la modification des paramètres de la boîte de dialogue Rechercher modifie les valeurs enregistrées qui sont utilisées si vous omettez les arguments.

Conseils : Pour éviter les problèmes, définissez explicitement LookIn, LookAt et SearchOrder chaque fois que vous utilisez la méthode Find.

Remarque : malheureusement, les valeurs actuelles utilisées dans Rechercher ne peuvent pas être récupérées (par exemple, à partir des paramètres de propriété de l'application), de sorte que vous ne pouvez pas les restaurer après utilisation. Heureusement, les valeurs reviennent à leurs valeurs par défaut après la fermeture d'Excel.

LookAt : Correspondre à l'ensemble du contenu de la cellule

Peut être l'une des constantes XlLookAt suivantes : xlWhole ou xlPart. Utilisez xlWhole si vous devez trouver une cellule contenant un texte spécifique.

Ajoutez MatchCase:=True si vous devez trouver une correspondance exacte dans la plage (sensible à la casse).


Dim rngFound As Range 
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookAt:=xlWhole, MatchCase:=True)

Utilisation de caractères génériques

Dans Excel, vous disposez d'un nombre limité de caractères génériques dans l'argument Find :

un point d'interrogation ( ?) correspond à un caractère dans une chaîne de caractères ;
L'insertion d'un point d'interrogation au début ou à la fin du texte est également valable s'il y a plus d'un caractère.
Un astérisque (*) correspond à n'importe quelle série de caractères.

Par exemple, le code ci-dessous recherche toute cellule qui correspond à "x".


Set rngFound = ActiveSheet.UsedRange.Find(What:="?x*")

La procédure TestFindAll (en bas de page) vous aide à déterminer les résultats attendus avec des arguments de recherche donnés. Elle écrit l' adresse de la plage dans la fenêtre immédiate : $B$1:$C$1,$C$2

basic data to test which cells match the criteria

Ordre de recherche : Lignes ou colonnes

Peut être l'une des constantes XlSearchOrder suivantes : xlByRows ou xlByColumns.

La recherche par colonne signifie que l'on recherche d'abord toutes les cellules de la colonne A, puis de la colonne B, etc.
La recherche par ligne signifie que l'on recherche d'abord toutes les cellules de la ligne 1, puis de la ligne 2, etc.


Dim rngFound As Range 
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByRows)

SearchDirection : Rechercher suivant

Le sens de la recherche et l'ordre de recherche déterminent les cellules suivantes. Par exemple, si la dernière cellule de UsedRange est B6 : B6, B5, B4...


Dim rngFound As Range
rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByColumns, _
										 SearchDirection:=xlPrevious)

Recherche d'une plage de critères multiples

Pour trouver des cellules qui correspondent à des modèles plus complexes, vous pouvez utiliser une instruction For Each...Next avec :

l'opérateur Like,
Appliquer des tests booléens combinés à l'aide de And, Or, etc.

Par exemple, le code suivant recherche toutes les cellules de la plage A1:C5 qui utilisent une police dont le nom commence par les lettres Cour. Lorsque Microsoft Excel trouve une correspondance, il remplace la police par Times New Roman.


Dim rngCells As Range
rngCells = ActiveSheet.UsedRange.Cells 
Dim rngCell As Range 
Dim rngFound As Range 
For Each rngCell In rngCells 
	If rngCell.Font.Name Like "Cour*" Alors
	Set rngFound = rngCell 
	Exit For 
	End If 
Next rngCell

SearchFormat : Format...

Renvoyer uniquement les cellules auxquelles un certain formatage a été appliqué (police, couleur d'arrière-plan,...). L'exemple ci-dessous montre que vous pouvez trouver toutes les cellules qui ont une couleur de fond en utilisant une chaîne vide pour le paramètre What. Décommentez . Color = 65535 pour obtenir les cellules marquées en jaune. Vous pouvez également filtrer les cellules avec Font.Boldou d'autres propriétés de la cellule.

Remarque : L'utilisation des deux méthodes What et FindFormat ne permet de trouver que les cellules qui correspondent aux deux critères.


Sub Demo() 
	With Application.FindFormat .Clear 
	' .Font.Bold = True 
		With .Interior .Pattern = xlSolid 
		'.Color = 65535 'rgbYellow 
		End With 
	End With 
	ActiveSheet.UsedRange.Find(After:=ActiveCell, What:="", SearchFormat:=True).Select 
	Application.FindFormat.Clear 
End Sub

N'oubliez pas d'effacer le format FindFormat (avant et après l'exécution de la recherche) : Effacer le format de recherche (avant et) après l'exécution de la recherche.

MatchByte : par exemple japonais et chinois

Utilisé uniquement si vous avez sélectionné ou installé la prise en charge des langues à deux octets. True pour que les caractères à deux octets correspondent uniquement aux caractères à deux octets. False pour que les caractères à deux octets correspondent à leurs équivalents à un octet.

Auteur : Mark Uildriks

Référence : Range.Find(Excel) | Microsoft Learn

Bouclez les cellules à l'aide de Find et FindNext pour obtenir plusieurs résultats de recherche.

La méthode FindNext de Range

Vous pouvez utiliser la méthode FindNext (ou FindPrevious) ci-dessous pour répéter la recherche discutée précédemment. FindNext poursuit une recherche entamée avec la méthode Find. Elle trouve la cellule suivante qui répond aux mêmes conditions et renvoie un objet Range qui représente cette cellule.

Le code ci-dessous montre une boucle qui se poursuit jusqu'à ce que la recherche soit terminée. Lorsque la recherche atteint la fin de la plage de recherche spécifiée, elle revient au début de la plage. Pour arrêter une recherche lorsque cette boucle se produit, enregistrez l'adresse de la première cellule trouvée, puis testez chaque adresse de cellule trouvée successivement par rapport à cette adresse enregistrée.


Dim rngToSearch As Range: Set rngToSearch = ActiveSheet.UsedRange
Dim strWhat As String: strWhat = "test"
Dim rngFound As Range
Set rngFound = rngToSearch.Find(What:=strWhat)
If Not rngFound Is Nothing Then
	Dim FirstFoundCell As String
	FirstFoundCell = rngFound.Address
	Do
		Debug.Print rngFound.Address
		Set rngFound = rngToSearch.FindNext(After:=rngFound)
	Loop While FirstFoundCell <> rngFound.Address
End If

Gestion des erreurs

Avant de poursuivre la recherche, le code ci-dessus évite l'erreur en vérifiant que la variable a été instanciée : If Not rngFound Is Nothing Then. Si cela n'est pas fait, tôt ou tard, lorsque vous appliquerez certaines procédures de la classe, le code rencontrera l'erreur 91 : Object variable or With block variable not Set.

L'argument After doit être une seule cellule de la plage. Si ce n'est pas le cas, vous obtenez l' erreur 13 : Type mismatch.

Ajouter une gestion des erreurs En particulier, si vous avez l'intention de laisser d'autres utilisateurs utiliser votre macro, il est judicieux d' ajouter un code de gestion des erreurs.

Une méthode FindAll pour l'objet Range

Il n'existe pas de méthode FindAll intégrée. Cela dit, sur la base du code ci-dessus, nous fournissons une fonction FindAll qui renvoie une plage contenant toutes les cellules correspondant aux critères de recherche, ainsi qu'une procédure permettant de tester FindAll avec différents paramètres (voir le code ci-dessous).

Bibliothèque Code VBA Le code est également inclus dans le complément Code VBA Editor qui peut être téléchargé ici.


Public Function FindAll(Range As Range, What As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, _
    Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
    Dim FoundCell As Range, FirstFound As Range, ResultRange As Range
    Dim LastCell As Range: Set LastCell = Range.Cells(Range.Cells.Count)
    Set FoundCell = Range.Find(What:=What, After:=LastCell, LookIn:=LookIn, LookAt:=LookAt, _
                                SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    If Not FoundCell Is Nothing Then
        Set FirstFound = FoundCell
        Set ResultRange = FoundCell
        Set FoundCell = Range.FindNext(After:=FoundCell)
        Do Until (FoundCell.Address = FirstFound.Address)
            Set ResultRange = Application.Union(ResultRange, FoundCell)
            Set FoundCell = Range.FindNext(After:=FoundCell)
        Loop
    End If
    Set FindAll = ResultRange
End Function


Sub TestFindAll(What As Variant, _
	Optional LookIn As XlFindLookIn = xlValues, _
	Optional LookAt As XlLookAt = xlWhole, _
	Optional SearchOrder As XlSearchOrder = xlByRows, _
	Optional MatchCase As Boolean = False)
Dim rngUsedRange As Range: Set rngUsedRange = ActiveSheet.UsedRange
Dim rngSearchResults As Range
Set rngSearchResults = FindAll(Range:=rngUsedRange, What:=What, LookIn:=LookIn, _
				LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
Debug.Print rngSearchResults.Address
rngSearchResults.Select
End Sub