Microsoft Office Tutorials and References
In Depth Information
Try It
7.
Declare a String type variable for the value to be located:
Dim FindWhat As String
8.
Define the FindWhat variable as an InputBox entry:
FindWhat = _
InputBox(“What do you want to find?”, “Find what?”)
9.
If the Cancel button is clicked, or nothing is entered in the InputBox, exit the macro:
If FindWhat = “” Then Exit Sub
10.
Set the varFind variable to the Find method:
Set varFind = _
Cells.Find(What:=FindWhat, LookIn:=xlFormulas, lookat:=xlWhole)
11.
If varFind is Nothing , inform the user that the value being looked for was not found. Also,
exit the macro.
If varFind Is Nothing Then
MsgBox _
FindWhat& “ was not found.”, _
vbInformation, _
“No such animal.”
Exit Sub
Else
12.
Define the row and column variables to identify the found cell. Actually, this is not required
because varFind , being Variant , would identify the found cell address with varFind.Address .
The row and column variables are for demonstration purposes.
varFindRow = varFind.Row
varFindColumn = varFind.Column
13.
A Message Box informs the user that the value was found, and in what cell:
MsgBox FindWhat& “ was found in cell “ & _
Cells(varFindRow, varFindColumn).Address & “.”, , “Found”
14.
Enter the End If statement:
End If
15.
Press Alt+Q to return to the worksheet and test your macro. The entire macro when it is
completed will look like this:
Sub FindTest()
‘Declare a variant type variable for the value to locate.
Dim varFind As Variant
Search JabSto ::




Custom Search