Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

How to: Search for Text in Worksheet Ranges

NoteNote

Some code examples in this topic use the this or Me keyword or the Globals class in a way that is specific to document-level customizations, or they rely on features of document-level customizations such as host controls. These examples can be compiled only if you have the required applications installed. For more information, see Features Available by Product Combination.

The Find method of the Microsoft.Office.Interop.Excel.Range object enables you to search for text within the range.

The following example searches a range named Fruits and modifies the font for cells containing the word "apples". This procedure also uses the FindNext method, which uses the previously set search settings to repeat the search. You specify the cell after which to search, and the FindNext method handles the rest.

NoteNote

The FindNext method's search wraps back to the beginning of the search range once it has reached the end of the range. Your code must make sure that the search does not wrap around in an infinite loop. The sample procedure shows one way to handle this using the Address property.

By default, the search starts after the cell in the upper left corner of the range. It searches for the word "apples" in the cell values, matching partial values, searching by rows in a forward direction, and is not case sensitive.

To search for text in a worksheet Range

  1. Declare variables for tracking the entire range, the first found range, and the current found range.

    Excel.Range currentFind = null; 
    Excel.Range firstFind = null; 
    
    
  2. Search for the first match, specifying all the parameters except the cell to search after.

    currentFind = this.Fruits.Find("apples", missing,
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, 
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
        missing, missing); 
    
    
  3. Continue searching as long as there are matches.

    while(currentFind != null) 
    
    
  4. Compare the first found range (firstFind) to Nothing. If firstFind contains no value, the code stores away the found range (currentFind)

    if (firstFind == null)
    {
        firstFind = currentFind; 
    }
    
    
  5. Otherwise, if the address of the found range matches the address of the first found range, the code exits the loop.

    else if (currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing)
          == firstFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))
    {
        break;
    }
    
    
  6. Set the appearance of the found range.

    currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
    currentFind.Font.Bold = true; 
    
    
  7. Perform another search.

    currentFind = this.Fruits.FindNext(currentFind); 
    
    

The following example shows the complete method.

Example

private void DemoFind() 
{
    Excel.Range currentFind = null; 
    Excel.Range firstFind = null; 

    // You should specify all these parameters every time you call this method,
    // since they can be overridden in the user interface. 
    currentFind = this.Fruits.Find("apples", missing,
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, 
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
        missing, missing); 
    
    while(currentFind != null) 
    { 
        // Keep track of the first range you find. 
        if (firstFind == null)
        {
            firstFind = currentFind; 
        }
    
        // If you didn't move to a new range, you are done.
        else if (currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing)
              == firstFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))
        {
            break;
        }

        currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
        currentFind.Font.Bold = true; 

        currentFind = this.Fruits.FindNext(currentFind); 
    }
}

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2015 Microsoft