Search and Find Using VBA

December 2016

The Find function is one of the most common functions used in VBA. The method allows users to locate the first occurrence of a piece of information within a range. You can use this method to search in a sheet, or part of a Microsoft Excel spreadsheet. This tutorial will explain how to find a value in an Excel column using the Find function in VBA.


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.

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, 
SearchFormat:=False).Activate

Understanding 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.).

Returned 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.

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


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.

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
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, 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
Next
Else
GoTo Absent
End If
Find_Next = True
Exit Function
Absent:
Find_Next = False
End Function

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

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

By:
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

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

FindAll

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

Related :

This document entitled « Search and Find Using VBA » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.