Méthode find dans VBA - Recherche de données sous Excel

Document initial créé par Pijaku

Cette fiche pratique est essentiellement tirée de l'aide Excel. L'exemple d'utilisation est un conseil basé sur ma propre petite expérience.

Introduction

La méthode find permet de trouver la première occurrence d'une information spécifique, dans un range. En clair, cette méthode permet d'aller chercher dans une feuille ou une partie d'une feuille Excel, une information, une donnée ou une partie de cette donnée.

Méthode via Excel

• Ouvrez un classeur (contenant des données)

• Ctrl+F

  , correspond à :
• Excel < 2007 : Edition/Rechercher
• Excel >= 2007 : Onglet Accueil / Rechercher et Sélectionner / rechercher
• Saisir la valeur à rechercher, régler les options, puis cliquer sur suivant

Code VBA correspondant

L'enregistreur de macro nous donne le code suivant (mot cherché : « Valeur ») :

Cells.Find(What:="Valeur", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Syntaxe

<ital>MonRange</ital>.Find(
 What,
 After,
 LookIn,
 LookAt,
 SearchOrder,
 SearchDirection,
 MatchCase,
 SearchFormat)

MonRange : Expression qui représente ici l'objet Range dans lequel vous souhaitez chercher la valeur contenue dans « What ». Cela peut être votre feuille en entier (

Sheets(1).Cells.Find(...)

), une colonne (

Sheets(1).Columns(3).Find(...)

), une ligne (

Sheets(1).Rows(7).Find(....)

) ou une plage de cellules (

Sheets(1).Range("D12:F56").Find(....)

).

What : Obligatoire. De type variant, il s'agit de ce que vous cherchez dans votre range. What peut être de n'importe quel type de données qu'Excel est capable de traiter.

After : Facultatif. De type variant. Cellule après laquelle vous souhaitez commencer la recherche. Cela correspond à la position de la cellule active lorsqu'une recherche est effectuée à partir de l'interface utilisateur. Notez que l'argument Après doit correspondre à une seule cellule de la plage. Rappelez-vous que la recherche commence après cette cellule ; la cellule spécifiée n'est pas recherchée jusqu'à ce que la méthode revienne à cette cellule. 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.

LookIn : Facultatif. De type variant. Traduction instantanée : regarder dans. En effet, nous cherchons une valeur dans un Range, mais nous pouvons de plus spécifier dans quoi elle est contenue (Valeur : LookIn:=XlValues, Formule : LookIn:=XlFormulas)

LookAt :Facultatif. De type variant, il indique si la valeur trouvée doit être rigoureusement égale à la valeur cherchée, ou en partie seulement. Exemple, dans la matrice : 6210, 4105, 540, 163, 154, 132, 10, vous recherchez la valeur 10. Pour indiquer à VBA que vous cherchez exclusivement 10, vous utiliserez LookAt:=XlWhole. A contrario, pour lui indiquer que 6210 ou 4105 vous conviennent (elles contiennent toutes deux 10), vous utiliserez : LookAt:=XlPart.

SearchOrder : Facultatif. De type variant. L'ordre de recherche admet une des deux constantes : xlByRows (par lignes) ou xlByColumns (par colonnes).

SearchDirection : Facultatif. De type variant. Indique le sens de recherche lors de l'exploration d'une plage. Soit xlNext, recherche la valeur suivante dans la plage, soit xlPrevious, recherche la valeur précédente.

MatchCase : Facultatif. De type variant. Deux valeurs pour cet argument : par défaut False, et pour que la recherche respecte la casse, lui affecter la valeur True.

SearchFormat : Facultatif. De type variant. True ou False selon si vous affectez un formatage (monétaire, standard, nombre, bordure, remplissage, alignement...).

A noter : Un argument facultatif MatchByte de type variant, s'utilise si vous avez installé et utilisez la prise en charge des langues codées sur deux octets (False pour que les caractères codés sur deux octets correspondent à leurs équivalents codés sur un octet)

Valeur renvoyée

La méthode find renvoie l'objet Range représentant la cellule ou est trouvée la première occurrence de la valeur cherchée. Cette méthode renvoie Nothing si aucune correspondance n'est trouvée. Ceci est par conséquent source d'erreurs qu'il vous faudra traiter (voir exemple d'utilisation). La méthode Find n'affecte pas la sélection ni la cellule active.

A savoir

Les paramètres des arguments LookIn, LookAt, SearchOrder et MatchCase sont enregistrés chaque fois que vous utilisez cette méthode. Si vous ne spécifiez aucune valeur pour ces arguments lors du prochain appel à la méthode, les valeurs enregistrées sont utilisées. Le fait de définir ces arguments modifie les paramètres de la boîte de dialogue Rechercher, ce qui a pour effet de changer les paramètres enregistrés qui sont utilisés lorsque vous ne spécifiez pas les arguments. Pour éviter toute ambiguïté, définissez ces arguments explicitement chaque fois que vous utilisez cette méthode

Exemple d'utilisation

Option Explicit

Sub Cherche()
'déclaration des variables :
Dim Trouve As Range, PlageDeRecherche As Range
Dim Valeur_Cherchee As String, AdresseTrouvee As String

'********* à adapter ***********
'affectation de valeurs aux variables :
    'on cherche le mot "Trouve"
Valeur_Cherchee = "Trouve"
    'dans la première colonne de la feuille active
Set PlageDeRecherche = ActiveSheet.Columns(1)
'*******************************

'méthode find, ici on cherche la valeur exacte (LookAt:=xlWhole)
Set Trouve = PlageDeRecherche.Cells.Find(what:=Valeur_Cherchee, 
                     LookAt:=xlWhole)

'traitement de l'erreur possible : Si on ne trouve rien :
If Trouve Is Nothing Then
    'ici, traitement pour le cas où la valeur n'est pas trouvée
    AdresseTrouvee = Valeur_Cherchee 
                                & " n'est pas présent dans " 
                                & PlageDeRecherche.Address
Else
    'ici, traitement pour le cas où la valeur est trouvée
    AdresseTrouvee = Trouve.Address
End If
MsgBox AdresseTrouvee
'vidage des variables
Set PlageDeRecherche = Nothing
Set Trouve = Nothing
End Sub

Remarque : Si nous avions cherché le nombre 1024 au lieu du mot Trouve, il aurait fallu déclarer Valeur_Cherchee As Integer. What étant variant, vous pouvez chercher n'importe quel type de donnée Excel, du moment ou vous le spécifiez dans la déclaration de votre variable.

Recherche multiple

FindNext - FindPrevious

Les méthodes FindNext et FindPrevious permettent de répéter la recherche.
Lorsque la recherche atteint la fin de la plage de recherche spécifiée, elle revient au début de cette plage. Pour arrêter une recherche lorsqu'elle revient au point de départ, enregistrez l'adresse de la première cellule trouvée, puis comparez l'adresse de chaque cellule ultérieurement trouvée avec l'adresse enregistrée.
Vous pouvez trouver une fonction utilisant FindNext dans cette fiche de Lermitte222.

Une variante : Find_Next

Ce code de Michel_M, permet de lister les lignes d'une colonne dont les cellules contiennent la valeur recherchée. Dans l'exemple codé ici, nous allons chercher "mot" dans la plage "A1:A20".
Cela fonctionne pour toute plage de cellule ne comportant, bien sur, qu'une seule colonne, y compris pour une colonne entière.

Sub Principale()
Dim Plage As Range
Dim Lignes(), i As Long
Dim Texte As String
Dim Flag As Boolean

Set Plage = Sheets("Feuil1").Range("A1:A20") 'plage de recherche
Texte = "mot"   'expression cherchée
Flag = Find_Next(Plage, 
                            Texte, 
                             Lignes())  'appel de la fonction
'si fonction retourne Vrai = expression trouvée dans la plage
If Flag Then  
     'restitution des lignes correspondantes
    For i = LBound(Lignes) To UBound(Lignes)  
        Debug.Print Lignes(i)
    Next i
Else
    MsgBox "L'expression : " 
                  & Texte 
                  & " n'a pas été trouvée dans la plage : " 
                  & Plage.Address
End If
End Sub

'Sources : Michel_m
'http://www.commentcamarche.net/forum/affich-31432413-importation-de-donnees-sans-doublons#9
Function Find_Next(Rng As Range, Texte As String, Tbl()) As Boolean
Dim Nbre As Integer, Lig As Long, Cptr As Long

    Nbre = Application.CountIf(Rng, Texte)
    If Nbre > 0 Then
        ReDim Tbl(Nbre - 1)
        Lig = 1
        For Cptr = 0 To Nbre - 1
            Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
            Tbl(Cptr) = Lig
        Next
    Else
        GoTo Absent
    End If
    Find_Next = True
    Exit Function
Absent:
    Find_Next = False
End Function

Nota 1 : Pour une colonne entière, dans la procédure d'appel, remplacez :

Set Plage = Sheets("Feuil1").Range("A1:A20")

par :

Set Plage = Sheets("Feuil1").Columns(1)

Nota 2 : Pour retourner les adresses des cellules plutôt que leur numéro de ligne, dans la fonction Find_Next, remplacez :

Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
Tbl(Cptr) = Lig

par :

Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
Tbl(Cptr) = Cells(Lig, Rng.Column).Address

FindAll

Sources.
Cette fonction personnalisée bien pratique retournant le résultat de Find et FindNext sous la forme d'un tableau de valeurs. Elle trouve toutes les instances d'un String (passé en paramètre de cette fonction : sText As String) et retourne un tableau contenant les numéros de ligne.
Les paramètres de cette fonction :
ByVal sText As String => La valeur recherchée,
ByRef oSht As Worksheet => la feuille ou chercher cette valeur,
ByRef sRange As String => le Range précis ou chercher la valeur,
ByRef arMatches() As String => la variable tableau ou vont être stockées les valeurs de retuor (lignes, adresses, etc...)
Merci à Jordane45 pour cette découverte.

Un exemple d'utilisation ICI.
Le code :

Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean
' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the1 given string and return the row numbers.
' If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim rFnd As Range ' Range Object
Dim iArr As Integer ' Counter for Array
Dim rFirstAddress ' Address of the First Find
' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(what:=sText, LookIn:=xlValues, lookAt:=xlPart)

If Not rFnd Is Nothing Then
   rFirstAddress = rFnd.Address
   Do Until rFnd Is Nothing
      iArr = iArr + 1
      ReDim Preserve arMatches(iArr)
      arMatches(iArr) = rFnd.Row 'rFnd.Address pour adresse complete ' rFnd.Row Pour N° de ligne
      Set rFnd = oSht.Range(sRange).FindNext(rFnd)
      If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search
   Loop
   FindAll = True
Else
' ----------------------
' No Value is Found
' ----------------------
   FindAll = False
End If
' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
   MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
   Err.Clear
   FindAll = False
   Exit Function
End If
End Function