VBA code to find cells in a range

The Find method of the Range class is used to find cells containing a given value or format in a range.

Find in worksheet

In its basic form Find is called like the code below, which selects the found cell. The code will only work if the value actually is present on the worksheet.


ActiveSheet.UsedRange.Find(What:="x").Select

Note: To keep the sample code short we use the ActiveSheet method to provide the parent Worksheet object whose Range class is being used. In real code using explicitly declared and assigned object variables is preferred, in the current case: Dim wsActiveSheet As Worksheet: Set wsActiveSheet = ActiveSheet , quite likely with another object assignment expression.

The Range class object here is obtained from the Worksheet UsedRange procedure.

The procedure returns an object of type Range which is optionally assigned to a variable rngFound. Generally, you will use a variable, here: strWhat to assign the What parameter.


Dim strWhat As String: strWhat = 
Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:=strWhat)

The Find method returns Nothing if no match is found.

Note: The Find method does not affect the selection or the active cell.

Find in range

Search for value in column

Below code lets you find a cell containing the value in a given column.


Dim rngColumn As Range
Set rngColumn = ActiveSheet.Columns(3)
Dim rngFound As Range
Set rngFound = rngColumn.Find(What:="x").Select

Search row for value

Use the following code to find a cell containing the value in a row.


Dim rngRow As Range
Set rngRow = ActiveSheet.Rows(8)
Dim rngFound As Range
Set rngFound = rngRow.Find(What:="x")

What: specify search criteria

The Excel Find Method parameters corresponds largely to the Find dialog options.

Find what

The data to search for. Can be a string or any Microsoft Excel data type. Some advice on suitable parameter values:

When searching numeric values use Lookin:=xlFormulas and LookAt:=xlWhole
When searching date values use Lookin:=xlFormulas and LookAt:=xlWhole. The supplied date should be like 25/02/2024 which you can get using FormatDateTime(dtDate, vbGeneralDate)

Warning:

When searching numeric values, LookAt:=xlPart may cause you to find more numbers than you want: if you search for 1 you will also get 301, 12 and do on.
If columns are smaller cells may not display the value, but ### instead. You may be safer by combining with Lookin:=xlFormulas

After: start cell for search

If After has been specified, the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell.

Note: If you do not specify this argument, the search starts after the cell in the upper-left corner of the range A1. This is different from using the Find dialog, which starts at the ActiveCell.

Error: If used, the After argument must be a single cell in the range. If not, you get error 13: Type mismatch.

Below code starts search after the active cell mimicking the Find Dialog


Dim rngFound As Range
Set rngFound = ws.UsedRange.Find(What:="x", After:=ActiveCell)

LookIn: Formulas, Values, Notes, Comments

Can be one of the following XlFindLookIn constants: xlFormulas (Formulas), xlValues (Values), xlComments (Notes), or xlCommentsThreaded (Comments).


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookIn:=xlComments)

xlFormulas vs xlValues

The difference between Find Formulas or Values could be observed experimentally. Put in A1 value x and in B1 formula =A1. In the Find Dialog press Find All. With Formulas selected only A1 is found while with Values both A1 and B1 are found.

Conclusion:

Formulas looks at what the cell contains as what is available in the Formula bar ;
Values matches on what is displayed as value in the grid cell.

Warning: The settings for LookIn, LookAt and SearchOrder are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.

Advice: To avoid problems, set LookIn, LookAt and SearchOrder explicitly each time you use the Find method.

Note: Unfortunately, the current values used in Find can't be retrieved (e.g from application property setting) so you can't restore it after use. Fortunately, the values return to their defaults after closing Excel.

LookAt: Match entire cell contents

Can be one of the following XlLookAt constants: xlWhole or xlPart. Use xlWhole if you need to find cell with specific text.

Add MatchCase:=True if you are required to find an exact match in the range (case-sensitive).


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookAt:=xlWhole, MatchCase:=True)

Using wildcards

In Excel you have a limited use of wildcard characters in the Find argument:

a question mark (?) matches one character if within a string;
putting a question mark at the begin or end matches also if there are more than 1 characters.
An asterisk (*) matches any series of characters.

As an example, below code finds any cell that matches 'x'.


Set rngFound = ActiveSheet.UsedRange.Find(What:="?x*")

The TestFindAll procedure (bottom of the page) helps you determine what results may be expected with given find arguments. It writes the Address of the range to the Immediate window: $B$1:$C$1,$C$2

basic data to test which cells match the criteria

SearchOrder: Rows or Columns

Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

Search by column means search first all cells in column A, then column B etc.
Search by row means search first all cells in row 1, then row 2 etc.


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByRows)

SearchDirection: Find Next

The search direction together with SearchOrder will determine What will be next cells. Below combination will move up in columns upward, e.g. assuming UsedRange last cell is B6: B6, B5, B4...


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByColumns, _
											SearchDirection:=xlPrevious)

Range find multiple criteria

To find cells that match more complicated patterns you can use a For Each...Next statement with:

the Like operator,
Apply boolean tests combined using And, Or, etcetera.

For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When Microsoft Excel finds a match, it changes the font to Times New Roman.


Dim rngCells As Range
Set rngCells = ActiveSheet.UsedRange.Cells
Dim rngCell As Range
Dim rngFound As Range
For Each rngCell In rngCells
	If rngCell.Font.Name Like "Cour*" Then
		Set rngFound = rngCell
		Exit For
	End If
Next rngCell

SearchFormat: Format...

Return cells only with certain formatting applied (font, background color,...). Below example shows you can find all cells that have a background color by using an empty string for the What parameter. Uncomment .Color = 65535 to get the cells marked yellow. Alternatively you could filter on cells made Font.Bold or other properties of the cell.

Note: Using both What and FindFormat finds only cells that match both criteria.


Sub Demo()
With Application.FindFormat
	.Clear
'    .Font.Bold = True
	With .Interior
		.Pattern = xlSolid
        '.Color = 65535 'rgbYellow 
	End With
End With
ActiveSheet.UsedRange.Find(After:=ActiveCell, What:="", SearchFormat:=True).Select
Application.FindFormat.Clear
End Sub

Don't forget: Clear the FindFormat (both before and) after running the Find.

MatchByte: e.g. Japanese and Chinese

Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

Author: Mark Uildriks

Reference: Range.Find(Excel) | Microsoft Learn

Loop over cells using Find and FindNext to get multiple searchresults.

The Range FindNext method

You can use the FindNext (or FindPrevious) method below to repeat the search discussed previously. FindNext continues a search that was begun with the Find method. It finds the next cell that matches those same conditions and returns a Range object that represents that cell.

The code below shows a loop that continues until the search is complete. When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.


Dim rngToSearch As Range: Set rngToSearch = ActiveSheet.UsedRange
Dim strWhat As String: strWhat = "test"
Dim rngFound As Range
Set rngFound = rngToSearch.Find(What:=strWhat)
If Not rngFound Is Nothing Then
	Dim FirstFoundCell As String
	FirstFoundCell = rngFound.Address
	Do
		Debug.Print rngFound.Address
		Set rngFound = rngToSearch.FindNext(After:=rngFound)
	Loop While FirstFoundCell <> rngFound.Address
End If

Error handling

Before the actual continuing search, The above code prevents the error by checking the variable has been instantiated: If Not rngFound Is Nothing Then. If this is not done, sooner or later when you apply some of the class' procedures, the code will run into error 91: Object variable or With block variable not set.

The After argument must be a single cell in the range. If not, you get error 13: Type mismatch.

Add error handling Especially if you intend to let other users have your macro, it makes sense to add error handling code.

A Range FindAll method

There is no built-in FindAll method. That said, based on above code we provide a FindAll function that returns a range with all cells that match the search criteria and a procedure to test FindAll with different settings, code below.

Code VBA library The code is also included in the Code VBA Editor add-in which can be downloaded here.


Public Function FindAll(Range As Range, What As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, _
    Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
    Dim FoundCell As Range, FirstFound As Range, ResultRange As Range
    Dim LastCell As Range: Set LastCell = Range.Cells(Range.Cells.Count)
    Set FoundCell = Range.Find(What:=What, After:=LastCell, LookIn:=LookIn, LookAt:=LookAt, _
                                SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    If Not FoundCell Is Nothing Then
        Set FirstFound = FoundCell
        Set ResultRange = FoundCell
        Set FoundCell = Range.FindNext(After:=FoundCell)
        Do Until (FoundCell.Address = FirstFound.Address)
            Set ResultRange = Application.Union(ResultRange, FoundCell)
            Set FoundCell = Range.FindNext(After:=FoundCell)
        Loop
    End If
    Set FindAll = ResultRange
End Function


Sub TestFindAll(What As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False)
    Dim rngUsedRange As Range: Set rngUsedRange = ActiveSheet.UsedRange
    Dim rngSearchResults As Range
    Set rngSearchResults = FindAll(Range:=rngUsedRange, What:=What, LookIn:=LookIn, _
                            LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    Debug.Print rngSearchResults.Address
    rngSearchResults.Select
End Sub