Find worksheet cells that match specified criteria

One of the more elementary VBA tasks that any developer will
perform is to find cells that meet some criteria. VBA and Excel support only a
primitive method, the Range.Find method that requires some amount of
understanding. To further compound the problem the documentation leaves a lot
to be desired.

Then, a “find all”
capability. Microsoft enhanced the user interface to include a “find all”
capability. Unfortunately, and some versions after it was introduced in the
UI, it is still absent from the Excel object model. So, we look at
programmatically implementing a “find all” capability at the worksheet level.

The data to search for. Can be a string or any Microsoft
Excel data type. When searching for all cells that match a particular
format, use a zero length string (i.e., "") for this argument.

After

Optional

Variant

The cell after which you want the search to begin. This
corresponds to the position of the active cell when a search is done from the
user interface. After must be a single
cell in the range. Remember that the search begins after this cell;
the specified cell isn’t searched until the method wraps back around
to this cell. If you do not specify this argument, the search starts after
the cell in the upper-left corner of the range.

LookIn

Optional

Variant

The type of information. Can be xlValues, xlFormulas,
or xlComments.

LookAt

Optional

Variant

Can be one of the following XlLookAt
constants: xlWhole
or xlPart.

SearchOrder

Optional

Variant

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

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.

SearchFormat

Optional

Variant

Apply search format. Can be True or False.
See Remarks for more on how to use SearchFormat.

Return Value

A Range
object that represents the first cell where that information is found.

Remarks

This method returns Nothing
if no match is found. The Find
method does not affect the selection or the active cell.

The settings for LookIn, LookAt, SearchOrder, and MatchByte 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. To avoid problems, set these arguments
explicitly each time you use this method.

Unfortunately, FindNext does not respect the SearchFormat
specification. So, if the search criteria include format information, you
cannot use FindNext to continue the search. See the section on using the SearchFormat argument for more on how to
correctly find all cells.

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.

The below example lists the addresses of all the cells in
the range A1:A500 on worksheet 1 that contain the value 5.

With Worksheets(1).Range("a1:a500")

Dim C As Range

Set C = .Find(5, LookIn:=xlValues)

If Not C Is Nothing Then

Dim FirstAddress As String, Rslt As String

FirstAddress = C.Address

Do

Rslt = Rslt & C.Address & ","

Set C = .FindNext(C)

Loop While C.Address <> FirstAddress

Debug.Print Left(Rslt, Len(Rslt) - 1)

End If

End With

To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. 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 the code finds a match, it
changes the font to Times New Roman.

For Each c In [A1:C5]
If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New
Roman"
Next c

Processing a large number of cells with a ‘For…Each’ loop may
consume significant computer resources. Consequently, a consumer may perceive
the behavior as an unresponsive program.

Using the SearchFormat argument

The SearchFormat argument facilitates a search for cells
that match a desired format using a two-step process:

1)Specify
the desired format through the CellFormat object returned by the
Application.FindFormat property

2)In
the Find method specify the SearchFormat argument as True

For example, to find cells
that match a particular number format, use

Application.FindFormat.NumberFormat =
"General;-General;""-"""

Another example might be to display the address of the first
cell that has a particular fill, say the ColorIndex 19, starting after the
active cell.

With Application.FindFormat

.Clear

.Interior.ColorIndex = 19

End With

MsgBox Cells.Find(What:="",
After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=True).Address

To specify the fill color, use ColorIndex or Color or
ThemeColor. What is peculiar is that while Interior.ThemeColor works as
expected, specifying a TintAndShade causes the Find method to fail. So, the
below does not work.

With Range("c6").Interior

.ThemeColor = xlThemeColorAccent2

.TintAndShade = 0.5

End With

With Application.FindFormat

.Clear

With .Interior

.ThemeColor = xlThemeColorAccent2

.TintAndShade = 0.5

End With

End With

MsgBox Cells.Find(What:="",
After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=True).Address

As noted above FindNext will not yield the expected result when
searching for a format. So, one is forced to use the Find method inside the
loop as in the example below, which lists the addresses of all the cells in the
active worksheet that have a fill of red.

With Application.FindFormat

.Clear

With .Interior

.Color = RGB(255, 0, 0)

End With

End With

Dim FirstCell As Range

Set FirstCell = Cells.Find(What:="",
After:=Cells(1, 1), _

LookIn:=xlFormulas, LookAt:=xlPart, _

SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=True)

If Not FirstCell Is Nothing Then

Dim CurrCell As Range, Rslt As String

Set CurrCell = FirstCell

Do

Rslt = Rslt & CurrCell.Address &
","

Set CurrCell = Cells.Find(What:="",
After:=CurrCell, _

LookIn:=xlFormulas, LookAt:=xlPart, _

SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=True)

Loop Until CurrCell.Address = FirstCell.Address

Debug.Print Left(Rslt, Len(Rslt) - 1)

End If

Note that this approach, using the FindFormat property and the
SearchFormat argument, will not find cells that display a format that is
the result of conditional formatting.

Additional Information

For more on using the CellFormat object, see the Office 2013
documentation:

The FindAll function

One of the things I hate about the Range collection's Find
method is how cumbersome it is to set up and use. Not only is it clumsy to
detect when one has processed all the cells that meet the find criteria but it
is also not easy to get all the cells as a single range. The FindAll function
simplifies the use of the Find method.

Function FindAll(What, Optional SearchWhat As Variant, _

Optional LookIn, _

Optional LookAt, _

Optional SearchOrder, _

Optional SearchDirection As XlSearchDirection =
xlNext, _

Optional MatchCase As Boolean = False, _

Optional MatchByte, _

Optional SearchFormat) As Range

The function returns a range consisting of all the cells
that match the specified criteria. Consequently, it might contain multiple
areas.

FindAll supports all the same arguments that the Find method
with the exception that SearchWhat replaces the After argument. SearchWhat can
be

·nothing in which case the code searches the activesheet's
usedrange

·a specific worksheet or a single cell in which case the code
searches that worksheet's usedrange, or

·a specific range in which case the code searches just that range

To search for cells that meet a format criteria, use the
same approach as in the section discussing
the SearchFormat argument. Specifically, use the Application.FindFormat
property to specify the desired format. Then, use the FindAll function with
the SearchFormat argument set to True.

In the example below, the code searches column C of the used
range for all cells that match the specified number format.

Application.FindFormat.Clear

Application.FindFormat.NumberFormat =
"General;-General;""-"""

MsgBox FindAll("", Application.Intersect( _

ActiveSheet.UsedRange, Range("c:c")),
_

xlFormulas, xlPart, SearchFormat:=True).Address

The FindAll code and several examples

Below is the code for the FindAll function. There should be
no need to modify it for any custom criteria. It is general enough that it can
be used as a “drag and drop” solution.

Option Explicit

Function FindAll(What, Optional SearchWhat As Variant, _

Optional LookIn, _

Optional LookAt, _

Optional SearchOrder, _

Optional SearchDirection As XlSearchDirection =
xlNext, _

Optional MatchCase As Boolean = False, _

Optional MatchByte, _

Optional SearchFormat) As Range

'LookIn can be xlValues or xlFormulas, _

LookAt can be xlWhole or xlPart, _

SearchOrder can be xlByRows or xlByColumns, _

SearchDirection can be xlNext, xlPrevious, _

MatchCase, MatchByte, and SearchFormat can be True or
False. _

Before using SearchFormat = True, specify the
appropriate settings _

for the Application.FindFormat object, e.g., _

Application.FindFormat.NumberFormat =
"General;-General;""-"""

Dim aRng As Range

If IsMissing(SearchWhat) Then

On Error Resume Next

Set aRng = ActiveSheet.UsedRange

On Error GoTo 0

ElseIf TypeOf SearchWhat Is Range Then

If SearchWhat.Cells.Count = 1 Then

Set aRng = SearchWhat.Parent.UsedRange

Else

Set aRng = SearchWhat

End If

ElseIf TypeOf SearchWhat Is Worksheet Then

Set aRng = SearchWhat.UsedRange

Else

Exit Function '*****

End If

If aRng Is Nothing Then Exit Function '*****

Dim FirstCell As Range, CurrCell As Range

With aRng.Areas(aRng.Areas.Count)

Set FirstCell = .Cells(.Cells.Count)

'This little 'dance' ensures we get the first
matching _

cell in the range first

End With

Set FirstCell = aRng.Find(What:=What, After:=FirstCell,
_

LookIn:=LookIn, LookAt:=LookAt, _

SearchDirection:=SearchDirection,
MatchCase:=MatchCase, _

MatchByte:=MatchByte, SearchFormat:=SearchFormat)

If FirstCell Is Nothing Then Exit Function
'*****

Set CurrCell = FirstCell

Set FindAll = CurrCell

Do

Set FindAll = Application.Union(FindAll, CurrCell)

'Setting FindAll at the top of the loop ensures _

the result is arranged in the same sequence as _

the matching cells; the duplicate assignment of _

the first matching cell to FindAll being a small _

price to pay for the ordered result

Set CurrCell = aRng.Find(What:=What,
After:=CurrCell, _

LookIn:=LookIn, LookAt:=LookAt, _

SearchDirection:=SearchDirection,
MatchCase:=MatchCase, _

MatchByte:=MatchByte,
SearchFormat:=SearchFormat)

'FindNext is not reliable because it ignores the
FindFormat settings

Loop Until CurrCell.Address = FirstCell.Address

End Function

Below is a subroutine with several examples of how to use
FindAll.

Sub ExamplesOfFindAll()

'reset any prior find format condition

Application.FindFormat.Clear

'show the address of the range in the activesheet _

that contains a value of 1

MsgBox FindAll(1, , xlValues, xlWhole).Address

'show the address of the range in the activesheet _

that contains 1 as any part of the value

MsgBox FindAll(1, , xlValues, xlPart).Address

'show the address of the range in the activesheet _

where the formula contains a open paren

MsgBox FindAll("(", , xlFormulas,
xlPart).Address

'show the address of the cells in column C of the
activesheet _

that contain a zero

Application.FindFormat.Clear

Dim Rslt As Range

MsgBox FindAll(0, Range("c:c"), xlFormulas,
xlWhole).Address

'if a custom number format applies to the entire column
C, the below _

will cause a major performance headache because the
find will step _

through every cell in column C!

'MsgBox FindAll("", Range("c:c"), _

xlFormulas, xlPart, SearchFormat:=True).Address

'An alternative to the above is to limit the search to
the usedrange.

Application.FindFormat.Clear

Application.FindFormat.NumberFormat =
"General;-General;""-"""

MsgBox FindAll("", Application.Intersect( _

ActiveSheet.UsedRange, Range("c:c")),
_

xlFormulas, xlPart, SearchFormat:=True).Address

'show the address of the range in column C that
contains a zero and _

the specified custom number format

Application.FindFormat.Clear

Application.FindFormat.NumberFormat =
"General;-General;""-"""

MsgBox FindAll(0, Range("c:c"), _

xlFormulas, xlWhole, SearchFormat:=True).Address

'show the address of the range of cells in column C
within the _

activesheet's usedrange that have a fill color of
xlThemeColorAccent2

Application.FindFormat.Clear

Application.FindFormat.Interior.ThemeColor =
xlThemeColorAccent2

MsgBox FindAll("", Application.Intersect( _

ActiveSheet.UsedRange, Range("c:c")),
_

xlFormulas, xlPart, SearchFormat:=True).Address

End Sub

[1]
Most of the information for the Range.Find method is from the Microsoft online
documentation. I have added missing content, clarified other content, made
corrections, and provided additional information about the SearchFormat parameter.

By accessing any page or link on this web site other than this
page, you agree to the
terms and conditions.

Optional If you will take a moment to
provide your comments, it will help improve the site both
for you, and for other visitors.

Your browser does not support inline frames or is currently configured not to display inline frames.
aaaaa
Rate this pageYour browser does not support inline frames or is currently configured not to display inline frames.