The Code Net

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)

expression - Required. An expression that returns a Range object.
What - Required Variant. The data to search for. Can be a string or any Microsoft Excel data type.
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. Note that 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 don't specify this argument, the search starts after the cell in the upper-left corner of the range.
LookIn - Optional Variant. The type of information.
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.
SearchDirection - Optional XlSearchDirection. The search direction, can be one of these XlSearchDirection constants: xlNext (Default) or xlPrevious
MatchCase - Optional Variant. True to make the search case sensitive. The default value is False.
MatchByte - Optional Variant. Used only if you've 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

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.

Name:
Password:
Remember me?
Register

Login