How to: Search for Text in Worksheet Ranges

  • Article

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

  • Document-level projects

  • Application-level projects

Microsoft Office version

  • Excel 2003

  • Excel 2007

For more information, see Features Available by Application and Project Type.

The Find(Object, Object, Object, Object, Object, XlSearchDirection, Object, Object, Object) method of the Range object enables you to search for text within the range. This text can also be any of the error strings that can appear in a worksheet cell such as #NULL! or #VALUE!. For more information about error strings, see Cell Error Values.

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

Note

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

link to video For a related video demonstration, see How Do I: Use the Find Method in an Excel Add-in?.

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.

    Dim currentFind As Excel.Range = Nothing 
Dim firstFind As Excel.Range = Nothing 
Dim Fruits As Excel.Range = Me.Application.Range("A1", "B2")

    Excel.Range currentFind = null; 
Excel.Range firstFind = null;
Excel.Range Fruits = Application.get_Range("A1", "B3");

  2. Search for the first match, specifying all the parameters except the cell to search after.

    currentFind = Fruits.Find("apples", , _
    Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
    Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)

    currentFind = 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 Not currentFind Is Nothing

    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 Is Nothing Then
    firstFind = currentFind

    if (firstFind == null)
{
    firstFind = currentFind; 
}

  5. Exit the loop if the address of the found range matches the address of the first found range.

    ElseIf currentFind.Address = firstFind.Address Then 
    Exit While 
End If

    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.

    With currentFind.Font
    .Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
    .Bold = True 
End With

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

  7. Perform another search.

    currentFind = Fruits.FindNext(currentFind)

    currentFind = Fruits.FindNext(currentFind);

The following example shows the complete method.

Example

Private Sub DemoFind()
    Dim currentFind As Excel.Range = Nothing 
    Dim firstFind As Excel.Range = Nothing 
    Dim Fruits As Excel.Range = Me.Application.Range("A1", "B2")


    ' You should specify all these parameters every time you call this method,  
    ' since they can be overridden in the user interface.
    currentFind = Fruits.Find("apples", , _
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)

    While Not currentFind Is Nothing 

        ' Keep track of the first range you find. 
        If firstFind Is Nothing Then
            firstFind = currentFind

        ' If you didn't move to a new range, you are done. 
        ElseIf currentFind.Address = firstFind.Address Then 
            Exit While 
        End If 

        With currentFind.Font
            .Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
            .Bold = True 
        End With

        currentFind = Fruits.FindNext(currentFind)
    End While 
End Sub

private void DemoFind() 
{
    Excel.Range currentFind = null; 
    Excel.Range firstFind = null;
    Excel.Range Fruits = Application.get_Range("A1", "B3");


    // You should specify all these parameters every time you call this method, 
    // since they can be overridden in the user interface. 
    currentFind = 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 = Fruits.FindNext(currentFind); 
    }
}

See Also

Tasks

How to: Apply Styles to Ranges in Workbooks

How to: Refer to Worksheet Ranges in Code

Concepts

Working with Ranges

The Variable missing and Optional Parameters in Office Solutions