Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

The ultimate search.

written by Kid Van Ouytsel - Last updated Oct 2004

In addition to this article there are many examples of using the 'Find' method contained in the Examples workbook.

Introduction

When people want to search for an item in a range or worksheet they generally make use of a For...Next loop. Well, there is nothing wrong with a For...Next loop, or at least as long as the range we want to search in is very small. Once the range we want to search in gets bigger it's no longer advisable to make use of a For...Next loop, because of the time it will take.

Do we have an alternative?

Yes, of course we have! In Excel go to 'Edit' > 'Search' or push CTRL + F. Familiar with the pop-up window you get to see? We can use this search tool not only manually but also programmatically. And in this article I'm going to try to convince you why you should use it, and explain how.

Why should I use the Find method?

The main reason why you should use it is speed. If you programmatically want to search for an item in a wide range and you don't want to fall asleep in front of your monitor while your code is running, then you should definitely use the Find method.
Here's a little example to prove the above statement.

First of all put a value like "Yahoo, I'm here" in cell IV65536 of Sheet1 and run this little sub:

Sub DaQuick() 
     
    If Not Sheet1.Cells.Find("Yahoo, I'm here") Is Nothing Then MsgBox "I found you" 
     
End Sub 

Now, if you need to go to the restroom, run this sub:

Sub DaSloooooow() 
    Dim R As Range 
     
    For Each R In Sheet1.Cells 
        If R.Value = "Yahoo, I'm here" Then MsgBox "I found you" 
    Next R 
     
End Sub 

Convinced?

As you noticed, the first sub finds the item as fast as lightning, while the second one takes veeeeery long (1 min 57 sec on my 2.4Ghz system!). You can "find" more speed tests that compare different search methods in the Search & Find Examples workbook.

Ok, the Find method is fast, but how does it work?

IFirst of all, let's take a look at the Find method arguments. From the Excel VBA help files:

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

Remarks

The settings for LookIn, LookAt, SearchOrder, MatchByte, and the item searched for are saved each time you use this method. If you don't specify values for these arguments, the saved values will be used the next time you call the method. 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!

Aha, that makes things more clear, but how do I use it?

There are many different ways to use the Find method, but let's take a look at the most basic way to start with:

 
If Not Sheet1.Cells.Find("Yahoo, I'm here") Is Nothing Then MsgBox "I found you" 
 

In the above piece of code we test if a given string occurs in a worksheet. If the item can't be found the code returns nothing, otherwise it shows a message. This code is comparable with Application.Worksheetfunction.CountIf. We only test if a given item is available, and nothing more.

If we want the Find method to return more useful info then we need to declare and handle the result as a (single cell) range.

 
Dim R As Range 
 
Set R = Range("A1:B1234").Find("DaDamnMethod") 
 'If a match is found then return the value of the cell at the right of R
If Not R Is Nothing Then MsgBox R.Offset(0, 1).Value 
 
Set R = Nothing 
 

Once we have done that, we can handle that range as any other range in Excel. Format it, delete, change or edit its contents, you name it.

Unlike the above examples, the next one will return an error if no match is found, and thus is not as solid code (bad practice...).

Cells.Find("YouNameIt").Offset(3, 2).Select 

What if I want to search for multiple occurrences of an item?

If we want to search for multiple occurrences of an item, we need to use the FindNext or FindPrevious method and a little Do...While loop to repeat the search. Again a little example:

 
Sub WhereIsIt() 
    Dim R As Range, FindAddress As String 
     
     'Set the range in which we want to search in
    With Sheet1.Range("A1:N300") 
         'Search for the first occurrence of the item
        Set R = .Find("Aha, here it is") 
         'If a match is found then
        If Not R Is Nothing Then 
             'Store the address of the cell where the first match is found in a variable
            FindAddress = R.Address 
            Do 
                 'Color the cell where a match is found yellow
                R.Interior.ColorIndex = 6 
                 'Search for the next cell with a matching value
                Set R = .FindNext(R) 
                 'Search for all the other occurrences of the item i.e.
                 'Loop as long matches are found, and the address of the cell where a match is found,
                 'is different from the address of the cell where the first match is found (FindAddress)
            Loop While Not R Is Nothing And R.Address <> FindAddress 
        End If 
    End With 
     
     'Clear memory
    Set R = Nothing 
     
End Sub 

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, we save the address of the first found cell, and then test the address of each successive found cell against this saved address. An example of the use of the FindPrevious method can be found in the Search & Find Examples workbook.

But in case we want to edit or delete found items, the above code will return an error once we have changed or deleted all items found. In that case, at a certain point the code will no longer find matches, since we changed or deleted all the initially available items and the code will never be able to return to the cell where the first match was found. In such a case we need to replace:

Loop While Not R Is Nothing And R.Address <> FindAddress 

With:

 
 'If no more match is found, then exit sub
If R Is Nothing Then Exit Do 
 'Loop as long the address of the cell where a match is found,
 'is different from the one where the first match was found
Loop While R.Address <> FindAddress 

Are there other things I need to know about the Find method ?

We can prevent users from seeing that you have used the Find method, and what your code has been searching for, by resetting the Find dialog box to its default settings. To achieve that, we could add an extra line of code at the end of our search procedure:

 
Dim R As Range 
 
Set R = Range("A1:B1234").Find("Patatten en sausissen", LookIn:=xlFormulas, lookat:=xlPart) 
 'If a match is found then return the value of the cell at the right of R
If Not R Is Nothing Then MsgBox R.Offset(0, 1).Value 
 'Reset the Find dialog to it's default settings
Set R = Cells.Find("", LookIn:=xlValues, lookat:=xlWhole, _ 
searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) 
 
Set R = Nothing 
 

This article should have provided you with enough information and examples to allow you to use the find method successfully in your own workbooks. If there is anything you would like to see covered in more detail or requires clarification please email suggestions@markrowlinson.co.uk.