Microsoft Office Tutorials and References
In Depth Information
Try It
Try iT
In this lesson, you create a macro that avoids a runtime error while using the Find method to locate
a value on your worksheet. If the value is found, its cell address will be displayed in a Message Box.
lesson requirements
It is not practical to loop through potentially millions of cells, so the Find method is used with an
error bypass structure.
If you were to record a macro to find the word Hello on a worksheet, the recorded code would look
like this:
Cells.Find(What:=”Hello”, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If the word Hello is not found on the worksheet, a runtime error would result because the recorded
code is instructing VBA to activate a cell that contains a value that does not exist. The purpose of this
lesson is to avoid a runtime error if the value being looked for does not exist on the worksheet. To get
the sample database files you can download Lesson 17 from the book’s website at www.wrox.com .
step-by-step
1.
Open a workbook and activate a worksheet that contains a relatively large amount of data.
This is an exercise in finding a value if it exists on the worksheet, so the more complex the
worksheet, the better.
2.
From your worksheet press Alt+F11 to get into the Visual Basic Editor.
3.
From the menu bar, click Insert Module.
4.
In your new module, type the name of your macro as Sub FindTest and press the Enter key.
VBA will display your entry and new macro as follows:
Sub FindTest()
End Sub
5.
For your first line of code, declare a Variant type variable for the value you want to locate.
In this example, simply call it varFind .
Dim varFind as Variant
6.
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
Search JabSto ::




Custom Search