Microsoft Office Tutorials and References
In Depth Information
Try It
‘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
MsgBox _
FindWhat& “ was not found.”, _
vbInformation, _
“No such animal.”
Exit Sub
Else
‘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”
End If
End Sub
To view the video that accompanies this lesson, please select Lesson 17, available
at the following website: www.wrox.com/go/excelvba .
Search JabSto ::




Custom Search