You will find everything you need to know on the Excel VBA Find function. The Range Find function allows you to find cells within your Excel worksheet (and workbook) that contain a certain text or value. In this post let us explore the many ways in which you can use the Find function.
Find text in Excel
Before we show how to use VBA to search for text within an Excel spreadsheet let us first see how to do it Excel and explore the usually unknown features of the famous CTRL+F combo. See below where you can find it within the Home ribbon and Editing group.
By clicking the above or simply using the key combo CTRL+F we can enter the Find & Replace modal window.
As you notice above Excel easily finds 10 matches for the cells on the left. However there are several more interesting search combinations you can use, including usage of wildcards you can use to get more specific patterns. See some examples below:
As you might have already noticed I used 2 types of wildcards above:
- * – the asterisk symbol represents zero or many of any type of characters. It can be injected between characters to replace either no or any number of characters.
- ? – the question mark represents at least 1 character.
Now that we have a hand of the basic features of Excel in terms of word search let us move to how to use Excel VBA to find cells containing specific text.
VBA Range Find function
The VBA Find function is in fact a function of the Excel VBA Range object class. See Microsoft documentation for more details. A VBA Range represents any subset of cells within a spreadsheet – it can be a single cell, entire row or a patchwork of different cells and other Ranges. Executing the Find function in fact limits the search to only the cells within its Range object.
Below is the definition of the VBA Range Find function and its parameters:
.Find(What, [After] [LookIn], [LookAt], [SearchOrder], [SearchDirection], [MatchCase], [MatchByte], [SearchFormat])
The Find function returns only the first match within the Range. To search for next items you need to follow it up with the FindNext function.
|What||Required||The value you are searching for|
|After||Optional||The cell range from which you start your search from|
|LookIn||Optional||What to search in e.g. Formulas, Values or Comments – constants of XlFindLookIn: xlValues, xlFormulas, xlComments, xlCommentsThreaded|
|LookAt||Optional||Whether to search in a part of the string in a cell or whether it needs to match the entire cell string – constants of XlLookAt: xlWhole, xlPart|
|SearchOrder||Optional||The sequence of the search i.e. whether to search by rows or columns – constants of XlSearchOrder: xlByRows or xlByColumns|
|SearchDirection||Optional||Whether to search forward (next) or backwards (previous) – constants of XlSearchDirection: xlNext, xlPrevious|
|MatchCase||Optional||Case sensitive or not – True or False|
|MatchByte||Optional||Used for double byte languages. True to have double-byte characters match only double-byte characters – True or False|
|SearchFormat||Optional||Allow searching by format. See Application.FindFormat – True or False|
VBA Find – simple example
We will start with a very simple VBA Range Find function example – searching for a single cell within the entire Excel active spreadsheet:
Dim ws As Worksheet Set ws = ActiveSheet Debug.Print ws.Cells.Find("some")
As you can see it found the first match within the Activesheet (currently open and top spreadsheet) and returned the found value.
VBA Find All
Finding all matches is a little more complicated in VBA than in Excel. We will need to use Do While loop to run via all matches:
Dim searchRange As Range, found As Range, firstFind As Range 'Set the search range to the entire worksheet Set searchRange = ActiveSheet.Cells 'Search for the first match Set found = searchRange.Find("some") 'Save the first found cell to check later whether we have completed the search Set firstFind = found 'Loop through all items using FindNext Range function Do If Not (found Is Nothing) Then Debug.Print found.Value Set found = searchRange.FindNext(found) End If Loop While Not (found = firstFind)
some text some other text someone something somedeal someones somerset someway somewhat somewhen
I highlighted above 2 key functions that were used the Range Find Function and the Range FindNext Function. As I mentioned above the Find function will only return the first match. To get next matches you need to run FindNext on the original range. This is I am executing FindNext on the searchRange variable and not the found variable.
Another interesting point to notice is the Do While…loop. Notice I am comparing the found variable to the firstFind variable. This is because when running FindNext it will at some point move to the first match once again and thus never end… it will just keep going in a cirle! Thus the loop is set to end once the FindNext function returns the same first cell.
Find using Wildcards
As mentioned above you can use 2 types of wildcards the asterisk * (zero or more characters) and the question mark ? (at least 1 character) to match slightly more complicates cases.
Dim ws As Worksheet Set ws = ActiveSheet Debug.Print ws.Cells.Find("some ?") 'Output: some text Debug.Print ws.Cells.Find("some*w") 'Output: someway
VBA Find with After
To remind the After parameter of the Excel VBA Range Find function defines where you will start your search. It is useful when you don’t want to redefine your range just for the purpose of the search activity. See the example below:
Debug.Print Range("B2:B5").Find("some ?", After:=Range("B3"))
As you can see below the Find function starts searching for the “some” string just after cell B3 i.e. it will start at B4 where it finds the first matching string.
Find After – avoid wrap around
Even if we specify that the VBA Range Find function should start searching for the string after a specified cell, it might wrap around back to the beginning of the Range we specified if it does not find a match when going down. See example below to understand:
Debug.Print Range("B2:B5").Find("some*text", After:=Range("B3"))
As you see the search started at B4 however once the search pattern “some*text” was not found until B5 the function resumed search on the remaining cells B2:B3 to find “some text”.
Find After Avoid wrapping using VBA Find
What to do to avoid this wrapping? We can check whether the found text is not within the preceding range using the Application.Intersect function.
If the found cell is before our After cell then we can handle it as such:
Set found = Range("B2:B5").Find("some*text", After:=Range("B3")) If Intersect(Range("B2:B3"), found) Is Nothing Then Debug.Print "Found text: " & found.Value Else Debug.Print "Text found is within excluded range" End If
Text found is within excluded range
However if the found cell is only After the cell we specified then it will show like this:
Set found = Range("B2:B5").Find("some", After:=Range("B3")) If Intersect(Range("B2:B3"), found) Is Nothing Then Debug.Print "Found text: " & found.Value Else Debug.Print "Text found is within excluded range" End If
Found text: someone
Find in Values, Formulas, Comments
The VBA Range Find function allows you not only to search within Values (the evalution of your Excel formulas). It can also search instead (or including) within Formulas, Comments and even Excel Threaded Comments.
Let us explore how to use the LookIn parameter to look into specific attributes of a cell.
In the code below we will search for the word dog within Values, Formulas, Notes and Threaded Comments (just the first one). We will return the address first. Notice that for Formulas the result was the same – this is because the Value and Formula is the same in this case.
Debug.Print Range("A1:D4").Find("dog", LookIn:=xlValues).AddressLocal 'Output: $A$2 Debug.Print Range("A1:D4").Find("dog", LookIn:=xlFormulas).AddressLocal 'Output: $A$2 - as the formula and value for "dog" are the same in this case Debug.Print Range("A1:D4").Find("This is a dog", LookIn:=xlFormulas).AddressLocal 'Output: $B$2 Debug.Print Range("A1:D4").Find("dog", LookIn:=xlNotes).AddressLocal 'Output: $C$2 Debug.Print Range("A1:D4").Find("dog", LookIn:=xlCommentsThreaded).AddressLocal 'Output: $D$2
The same code but this time returning the actual Value, Formula, Note or Comment:
Debug.Print Range("A1:D4").Find("dog", LookIn:=xlValues).Value 'Output: dog Debug.Print Range("A1:D4").Find("dog", LookIn:=xlFormulas).Formula2Local 'Output: dog Debug.Print Range("A1:D4").Find("This is a dog", LookIn:=xlFormulas).Formula2Local 'Output: =IF(A2="Dog", "This is a Dog","Other") Debug.Print Range("A1:D4").Find("dog", LookIn:=xlNotes).NoteText 'Output: This is a note about a dog Debug.Print Range("A1:D4").Find("dog", LookIn:=xlCommentsThreaded).CommentThreaded.Text 'Output: This is a threaded comment about a dog
Find After – avoid wrap around
Complex patterns for Find
In some cases the pattern you want to find might be more complicated such as e.g. looking for cells with any sequence of numbers, emails, addresses, phone numbers etc. In this case the VBA Range Find function will be too limited. However, there is a solution with the help of so call VBA Regular Expressions. Regular Expressions help define almost any search pattern and are widely used in other programming languages.
If you want to learn more read my VBA Regex Tutorial otherwise a very simple example below.
In below code snippet we would like to find only phone numbers – so we will create a simple expression that finds any sequence of digits.
'Define the Regular Expression Dim regex As Object Set regex = CreateObject("VBScript.RegExp") With regex 'We will look only for sequences of at least 1 digit .Pattern = "[0-9]+" End With 'Search in all cells within the Range Dim r As Range For Each r In Range("A1:D4") If regex.Test(r.Value) Then Debug.Print "Found a match: " & r.AddressLocal End If Next r
Found a match: $A$3