Microsoft Office Tutorials and References
In Depth Information
Declare a String type variable for the value to be located:
Dim FindWhat As String
Define the FindWhat variable as an InputBox entry:
FindWhat = _
InputBox(“What do you want to find?”, “Find what?”)
If the Cancel button is clicked, or nothing is entered in the InputBox, exit the macro:
If FindWhat = “” Then Exit Sub
Set the varFind variable to the Find method:
Set varFind = _
Cells.Find(What:=FindWhat, LookIn:=xlFormulas, lookat:=xlWhole)
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
FindWhat& “ was not found.”, _
“No such animal.”
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
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”
Enter the End If statement:
Press Alt+Q to return to the worksheet and test your macro. The entire macro when it is
completed will look like this:
‘Declare a variant type variable for the value to locate.
Dim varFind As Variant