Macro SVB Programs Examples - Searching a Spreadsheet's Cells for a Value

This example will search for a specific string with the active individual spreadsheet. If the string is located, the cell is highlighted and the macro continues its search through the remaining cells in the spreadsheet. In this example, an input box is displayed that prompts you for a value; once that value has been entered, the entire spreadsheet is searched and each occurrence is selected. 

Sub Main
Dim s As Spreadsheet 
Dim result As Range
Dim WordToFind As String

'Enter the value you want to search for. WordToFind = InputBox("Enter a value to search for:")
'String was empty or user pressed Cancel 

If WordToFind = vbNullString Then
MsgBox "No value entered" Exit Sub
End If

'get the active individual spreadsheet 'if there isn't one then quit
Set s = ActiveSpreadsheet
If s Is Nothing Then Exit Sub
Dim v As Integer Dim c As Integer Dim r As Range

'Loop through all of the cases, one at a time

For c = 1 To s.Cases.Count

'Loop through all of the variables, one at a time

For v = 1 To s.Variables.Count
Set r = s.Cells (c, v)
'grab the cell at the current variable and cell 
'and read its contents into a string 
'verify whether or not the string is what you 
'are searching for.

If r.Text = WordToFind Then
If result Is Nothing Then
Set result = r
Else
Set result = result.Combine(r)

End If
End If
Next v
Next c
If Not (result Is Nothing) Then

'if the text is what you were looking for then highlight it.

result.Select
End If
End Sub