Microsoft Office Tutorials and References
In Depth Information
‘Declare two more variables, both Long type, for the row
‘and column of the value if it is found.
Dim varFindRow As Long, varFindColumn As Long
‘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 = 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 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”
To view the video that accompanies this lesson, please select Lesson 17, available