How to: Programmatically Search for Text in Worksheet Ranges
The Find method of the Microsoft.Office.Interop.Excel.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.
Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2013 and Excel 2010. For more information, see Features Available by Office Application and Project Type.
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 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.
The FindNext 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 property.
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
Declare variables for tracking the entire range, the first found range, and the current found range.
Search for the first match, specifying all the parameters except the cell to search after.
Continue searching as long as there are matches.
Compare the first found range (firstFind) to Nothing. If firstFind contains no value, the code stores away the found range (currentFind).
Exit the loop if the address of the found range matches the address of the first found range.
Set the appearance of the found range.
Perform another search.
The following example shows the complete method.
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