Excel VBA Find Method

Excel Find Method in Excel VBA. How to Use Find in Excel VBA Macros

The Excel Find Method is an excellent tool to use when writing Excel VBA macros. Unfortunately most end up using a VBA loop instead of the Find Method. The syntax for the Find Method is as shown below:

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

Where "expression" is any valid range Object, e.g. Range("A1:A100"), Columns(2) etc. Also, a Range Object is returned whenever we use the Find Method. The Range Object returned will of course be the Range where the value being sought resides.

The single best way to get the code needed for the Find Method is to record a macro using it on any Excel Worksheet. You will end with code like shown below;

Cells.Find(What:="Cat", After:=ActiveCell, LookIn:=xlValues, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

From here we can modify the code to suit any exact needs. It is very important to understand that the settings LookIn, LookAt and SearchOrder are saved each time the Find Method is used. For this reason one should always specify these settings explicitly each and every time you use the Find Method. If you don't, you run the risk of using the Find Method with settings you were not aware of.

The After setting is also very important. Whichever cell is set here will be the last one searched and not the first as some may expect. For this reason, one should always set this explicitly each and every time you use the Find Method.

Yet another trap can be the incorrect use of the After:= setting. If the Range Object specified is NOT within the range you are using Find on, you will get an error. For example, if you wanted to find a value on another Worksheet (not the Active one), restrict the Find to, say Column A and then select the found cell, you could use;

Sub FindCatOtherSheet()

Dim rFound As Range



    On Error Resume Next

    With Sheet1

        Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _

            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

            , SearchFormat:=False)

    On Error GoTo 0

        If Not rFound Is Nothing Then Application.Goto rFound, True

    End With



End Sub

Note the use of .Cells(1,1) as the After:= setting. If this was ANY cell NOT within Column A the code would normally bug-out. However, the use of On Error Resume Next prevents this. BUT, despite that you will not be taken to the cell. Sheet1 is the worksheet code name.

The example code below shows how we can use the Find Method on any Excel Worksheet to locate all occurrences of the Word "Cat" add a cell comment to each cell.

Note the use of the COUNTIF Worksheet Function to restrict the Find to the exact number of the times the word "Cat" appears in Column 1. Note also the setting of a Range variable (rFoundCell) to the found cell each time the word "Cat" found. This same variable is then used in the After setting of the Find Method.

Sub Find_Bold_Cat()



Dim lCount As Long

Dim rFoundCell As Range



    Set rFoundCell = Range("A1")



        For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat")

            Set rFoundCell = Columns(1).Find(What:="Cat", After:=rFoundCell, _

                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _

                SearchDirection:=xlNext, MatchCase:=False)

                

             With rFoundCell

                 .ClearComments

                 .AddComment Text:="Cat lives here"

             End With

            

        Next lCount

End Sub