A newer version of this page is available. Switch to the current version.

Search

  • 2 minutes to read

To search for specific data in a range, worksheet or entire document, use the Range.Search, Worksheet.Search or Workbook.Search methods, respectively. To set options affecting search in a document, create an instance of the SearchOptions class and pass it as a parameter to the Search method. You can set the following advanced options.

Use the Range.Value property to replace values in cells that match the search term.

workbook.Calculate()
Dim worksheet As Worksheet = workbook.Worksheets("ExpenseReport")
workbook.Worksheets.ActiveWorksheet = worksheet

' Specify the search term.
Dim searchString As String = Date.Today.ToString("d")

' Specify search options.
Dim options As New SearchOptions()
options.SearchBy = SearchBy.Columns
options.SearchIn = SearchIn.Values
options.MatchEntireCellContents = True

' Find all cells containing today's date and paint them light-green.
Dim searchResult As IEnumerable(Of Cell) = worksheet.Search(searchString, options)
For Each cell As Cell In searchResult
    cell.Fill.BackgroundColor = Color.LightGreen
Next cell

The image below shows the result of executing the code (the workbook is opened in Microsoft® Excel®). Today’s date is located in the expense report and highlighted in light-green.

SpreadsheetDocServer_SearchResult