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
Copyright © 2021. Cloud Software Group, Inc. All Rights Reserved.