Excel VBA Find – How to find any value in a range of cells with VBA

Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. However, VBA provides a much more efficient way of accomplishing this using the Find method. In this article we’ll have a look at how to use the Range.Find Method in your VBA code. Here is the syntax of the method:

expression.Find (What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )

where:

What: The data to search for. It can be a string or any of the Microsoft Excel data types (the only required parameter, rest are all optional)

After: The cell after which you want the search to begin. It is a single cell which is excluded from search. Default value is the upper-left corner of the range specified.

LookIn: Look in formulas, values or notes using constants xlFormulas, xlValues, or xlNotes respectively.

LookAt: Look at a whole value of a cell or part of it (xlWhole or xlPart)

SearchOrder: Search can be by rows or columns (xlByRows or xlByColumns)

SearchDirection: Direction of search (xlNext, xlPrevious)

MatchCase: Case sensitive or the default insensitive (True or False)

MatchByte: Used only for double byte languages (True or False)

SearchFormat: Search by format (True or False)

All these parameters correspond to the find dialog box options in Excel.

Return Value: A Range object that represents the first cell where the data was found. (Nothing if match is not found).

Let us look at some examples on how to implement this. In all the examples below, we will be using the table below to perform the search on.

Example 1: A basic search

In this example, we will look for a value in the name column.

Dim foundRng As Range
Set foundRng = Range("D3:D19").Find("Andrea")
MsgBox foundRng.Address

 

The rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.

The output of this code will be the first occurrence of the search string in the specified range.

If the search item is not found then Find returns an object set to Nothing. And an error will be thrown if you try to perform any operation on this (on foundRng in the above example)

So, it is always advisable to check whether the value is found before performing any further operations.

Dim foundRng As Range
Set foundRng = Range("D3:D19").Find("Andrea")
If foundRng Is Nothing Then
MsgBox "Value not found"
Else
MsgBox foundRng.Address
End If

 

Let us know have a look at the optional parameters. To keep it simple, we will exclude the above error handling in the subsequent examples.

Example 2: Using after

    
Set foundRng = Range("D3:D19").Find("Andrea", Range("D6"<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>))
MsgBox foundRng.Address

OR

    
Set foundRng = Range("D3:D19").Find("Andrea", After:=Range("D6"))
MsgBox foundRng.Address

The highlighted cell will be searched for

Example 3: Using LookIn

Before seeing an example, here are few things to note with LookIn

  1. Text is considered as a value as well as a formula
  2. xlNotes is same as xlComments
  3. Once you set the value of LookIn all subsequent searches will use this setting of LookIn (using VBA or Excel)
'Look in values
Set foundRng = Range("A3:H19").Find("F4", , xlValues)
MsgBox foundRng.Address
'Output --> $B
'Look in values and formula
Set foundRng = Range("A3:H19").Find("F4", LookIn:=xlFormulas)
MsgBox foundRng.Address
'Output --> $B
'Look in values and formula (as previous setting is preserved)
Set foundRng = Range("H3:H19").Find("F4")
MsgBox foundRng.Address
'Output --> $H
'Look in comments
Set foundRng = Range("A3:H19").Find("F4", , xlNotes)
MsgBox foundRng.Address
'Output --> $D

Example 4: Using LookAt

'Match only a part of a cell
Set foundRng = Range("A3:H19").Find("John", , xlValues, xlPart)
MsgBox foundRng.Address
'Output --> $D
'Match entire cell contents
Set foundRng = Range("A3:H19").Find("John", LookAt:=xlWhole)
MsgBox foundRng.Address
'Output --> $D

LookAt setting too is preserved for subsequent searches.

Example 5: Using SearchOrder

'Searches by rows
Set foundRng = Range("A3:H19").Find("Sa", , , xlPart, xlRows)
MsgBox foundRng.Address
'Output --> $G
'Searches by columns
Set foundRng = Range("A3:H19").Find("Sa", SearchOrder:=xlColumns)
MsgBox foundRng.Address
'Output --> $F

SearchOrder setting is preserved for subsequent searches.

Example 6: Using SearchDirection

'Searches from the bottom
Set foundRng = Range("A3:H19").Find("Alexander", , , , , xlPrevious)
MsgBox foundRng.Address
'Output --> $D
'Searches from the top
Set foundRng = Range("A3:H19").Find("Alexander", SearchDirection:=xlNext)
MsgBox foundRng.Address
'Output --> $D

Example 7: Using MatchCase

'Match Case
Set foundRng = Range("A3:H19").Find("Sa", , , , xlRows, , True)
MsgBox foundRng.Address
'Output --> $F
'Ignore case
Set foundRng = Range("A3:H19").Find("Sa", MatchCase:=False)
MsgBox foundRng.Address
'Output --> $G

 

Example 8: Using SearchFormat

When you search for a format it is important to note that the format settings stick until you change them. So, before you use SearchFormat, it is a good practice to always clear any previous formats that have been set.

'Clear any previous format set
Application.FindFormat.Clear
'Set the format for find operation
Application.FindFormat.Interior.Color = 14348258
'Match format
Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=True)
MsgBox foundRng.Address
'Output --> $G
'Search without matching the format
Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=False)
MsgBox foundRng.Address
'Output --> $G
'Search only for cells of a particular format
Set foundRng = Range("B3:H19").Find("*", MatchCase:=True, SearchFormat:=True)
MsgBox foundRng.Address
'Output --> $B

Example 9: FindNext and FindPrevious

In all the previous examples, we have been looking for just the first occurrence of the search criteria. If you want to find multiple occurrences, we use the FindNext and FindPrevious methods. Both of these need a reference to the last cell found, so that the search continues after that cell. If this argument is dropped, it will keep returning the first occurrence.

'First occurrence
Set foundRng = Range("A3:H19").Find("Ms")
MsgBox foundRng.Address
'Output --> $C
'Find the next occurrence
Set foundRng = Range("A3:H19").FindNext(foundRng)
MsgBox foundRng.Address
'Output --> $C
'Find the previous occurrence again
Set foundRng = Range("A3:H19").FindPrevious(foundRng)
MsgBox foundRng.Address
'Output --> $C

If you are looking to find or list all files and directories in a folder / sub-folder, you can refer to the articles below:

Find and List all Files and Folders in a Directory

Excel VBA, Find and List All Files in a Directory and its Subdirectories

Leave a Reply

Your email address will not be published. Required fields are marked *