Search and find using VBA in Excel

Search and find using VBA in Excel

This tutorial will explain how to find a value in an Excel column using the Find function in VBA.

How to search data using Find?

The Find function works very similarly to a basic search function. If you're looking to perform a simple search of all data in a workbook, you can do so by simply pressing the [CTRL] + F keys on your keyboard. This will open up a search box. Simply type in the keyword or value you're searching for and hit Enter.

Excel will highlight all of the cells that correspond to your search. 

How to search data using the Find method in VBA?

Here is how the Find function would look in VBA. Note that the search term used for this example is Value:

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

How to understand the Find method?

Here is a quick breakdown of all of the parameters of the Find method.

MyRange.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, SearchFormat) 
  • MyRange: This expression represents the Range object, which designates in which cells you'd like to search for the value contained in What parameter.

This can be your full sheet (Sheets(1).Cells.Find(...)), a column (Sheets(1).Columns(3).Find(...)), a row (Sheets(1).Rows(7).Find(....)), or a range of cells (Sheets(1).Range("D12:F56").Find(....)).

  • What: This parameter is required. This parameter allows you to stipulate which value you want to find in your range. It can be of any data type supported by Excel.
  • After: This parameter is optional. This parameter indicates the starting cell for the search (note that the cell must be unique). If After is not specified, the search will begin in the upper left corner of the range.
  • LookIn: This parameter is optional. The parameter is used to locate a value in a range. Other variations of LookIn include xlValues, xlFormulas, and xlComments.
  • LookAt: This parameter is optional. LookAt indicates whether or not the value must be exactly equal to the value sought, or partially equal.

For example, if one were to search for the value "10" in a matrix including: 6210, 4105, 540, 163, 154, 132, 10, there are various methods you can use. To tell VBA that you only want the number 10, you can use the parameter LookAt:=XlWhole. In contrast, to indicate you are searching for values that contain 10 (in this case, 6210 or 4105), you can use LookAt:=XlPart.

  • SearchOrder: This parameter is optional. The search order is subject to two constants: xlByRows (for rows), or xlByColumns (for columns).
  • SearchDirection: This parameter is optional. This parameter indicates the direction of search in a particular range. The two constants are xlNext, which is used to search a subsequent value in the range, and xlPrevious, which is used to search a value stated previously.
  • MatchCase: This parameter is optional. The two values for this argument are True and False. Case-sensitive searches should be set to True.
  • SearchFormat: This parameter is optional. The parameter can be True or False depending on whether or not a format is assigned (e.g. monetary standard, number, border, fill, alignment, etc.).

How to return value of Find method?

The Find method will return the Range object, which represents the cell in which a value first occurred. This method returns Nothing if no match is found. The parameters of the LookIn, LookAt, SearchOrder and MatchCase arguments are recorded each time you use this method. If you do not specify values for these arguments when using this method again, the stored values are used.

What are the examples of Find method in VBA?

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
    '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

N.B. If we had made a search for the 1024 number instead of the word "Find", we should have been declared Valeur_Cherchee as an integer. The What variant allows you to search for any type of data.

How to perform multiple searches in VBA?

The FindNext and FindPrevious methods allow you to perform multiple searches.

Using the Find_Next Variant

In the example coded here we will look for the word "mot" in the A1:A20 range:

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
If Flag Then  'si fonction retourne Vrai = expression trouvée dans la plage
    For i = LBound(Lignes) To UBound(Lignes)   'restitution des lignes correspondantes
        Debug.Print Lignes(i)
    Next i
    MsgBox "L'expression : " & Texte & " n'a pas été trouvée dans la plage : " & Plage.Address
End If
End Sub
'Sources : Michel_m
Function Find_Next(Rng As Range, Texte As String, Tbl()) As Boolean
Dim Nbre As Integer, Lig As Long, Cptr As Long, Adresse As String
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
        GoTo Absent
    End If
    Find_Next = True
    Exit Function
    Find_Next = False
End Function

N.B. For an entire column, simply replace:

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


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

N.B. To return the addresses of the cells rather than the line number in the Find_Next function, replace:

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


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


This custom function returns Find and FindNext results as an array of values. It will find all the instances of a string (sText As String) and returns an array containing the line numbers.

The parameters of this function are as follows:

  • ByVal sText As String represents the target value.
  • ByRef oSht As Worksheet represents the target sheet.
  • ByRef sRange As String represents the range.
  • ByRef arMatches() As String represents the array that will store the returned values.

The 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
FindAll = True
' ----------------------
' No Value is Found
' ----------------------
FindAll = False
End If
' -----------------------
' Error Handling
' -----------------------
If Err <> 0 Then
MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
FindAll = False
Exit Function
End If
End Function
Do you need more help with VBA? Check out our forum!
Around the same subject