Excel VBA Find Method

< Back to Search results

 Category: [Excel]  Demo Available 


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


                 .AddComment Text:="Cat lives here"

             End With


        Next lCount

End Sub


See also:

Excel: Get Data From Closed Excel Workbooks
Excel: Get Last Word From Text String
Extract Number From Text String
Excel VBA: Extract Word From a Text String
Excel VBA: Find Number Between 2 Numbers
Find Method to Find Dates


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. You can order a software analysis of your real estate at a custom software development agency in the UK and the USA.


stars (0 Reviews)