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