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 will be highlighted and the macro will continue its search through the remaining cells in the spreadsheet. In this example, an input box will be displayed that will prompt you for a value; once that value has been entered,  the entire spreadsheet will be searched and each occurrence will be 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