The Find Method

To find all the cells in a particular range that contain certain text, you can loop through all the cells in the range and test each cell’s Value property. But if the range is large, a loop like this can be slow. One alternative is the Find method. It is the VBA implementation of Edit > Find that we’ve all used in Excel’s user interface.

The Find method can be considerably quicker than looping, but there are a couple of things that you should know about Find. First, it will find the first cell in the range that meets your criteria. The FindNext method can then be used to find the next occurance. However, after FindNext has found the last occurence, executing it again will return to the start and find the first occurence all over again. This means that if you intend to find all ocurrences, you need to include a way for the cycle to stop.

The second thing you should know is how Excel treats Find’s arguments. Most methods have default argument values, so that when you omit them, you still know what they will be. Find is different in that it’s default arguments change every time it’s used. One of the arguments is LookAt and it’s used to identify whether you’re looking at whole cells or just part of the cells. If you execute Find and specify a LookAt of xlPart, then xlPart becomes the default for that argument. If you execute Find again and omit the LookAt argument, it will default to xlPart, which may be what you want. But maybe not – it pays to know.

I generally specify any arguments that are important to me and don’t rely on the default. LookAt is an example of an argument that I always specify. SearchOrder is an example of one that I rarely specify because, in most cases, I don’t care if it looks by rows or by columns. Be sure to check help to see all the arguments and what they do.

You should also note that the “shifting default” discussed above is not changed just by using Find in VBA. It is also changed when Edit > Find is used in the UI. That makes it particularly dangerous to rely on the defaults.

How about an example? Assume we have worksheet with a regional sales table on it. In column A is the region name and we need to highlight all the rows for the North region. Here’s one way you might do it.

4 thoughts on “The Find Method

  1. hi. I know this article is quite old. But i was wondering if you could help me. the FIND method is considerable slow if used in the background. What I mean is that if my application is in the foreground or the excel window is not visible, the range find is considerable slow. If you put focus on the excel window, then it is a lot faster. Did you ever observe this behavior?

    I have a function that uses the find method to find certain ranges to replace values in the excel sheet from a database and then print the data. i used late binding. code is:

    public bool PrintAll(string printer, int copies, DataTable records)
    {
    DataColumnCollection columns = records.Columns; //get all the columns of the datatable
    SortedList dataToInput = new SortedList(); //this array will contain the columns that can be found in the template

    //located locations of each variable to replace in excel template
    foreach(DataColumn col in columns)
    {
    ArrayList temp = Find(“@@” + col.ColumnName);
    if (temp.Count > 0)
    {
    string addresses = “”;
    foreach (object i in temp)
    {
    addresses += i.ToString() + “,”;
    }
    addresses = addresses.TrimEnd(‘,’);
    addresses = addresses.Replace(“$”,””);
    dataToInput.Add(col.ColumnName, addresses);
    }
    }
    if (dataToInput.Count == 0)return false;

    //replace the values in excel then print

    DataRowCollection rows = records.Rows;
    foreach (DataRow row in rows)
    {
    for (int x = 0; x

  2. Hi,

    I’m creating a userform in Excel, and I want to have a user enter a text string, and push a button to search the address column for that text string.

    How do I code the command button to search the address column for that text string??

    Any help would be greatly appreciated….

    Thanks!!

    Brad

  3. hi,

    I need some help writting a code…

    I have to compare two different worksheets in the some workbook by rows. And I need the different rows to be changed into a different color.

    Regards,

    Afonso

  4. I need to go back and read all of this blog! I started in mid-2006. This would have saved me from a serious headache. The default of LookAt changing with the coices made in the UI is a real subtlety!!

    Brett


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.