Microsoft Office Tutorials and References
In Depth Information
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.
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
Cells.Find(What:=”Hello”, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
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
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.
From your worksheet press Alt+F11 to get into the Visual Basic Editor.
From the menu bar, click Insert ➪ Module.
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:
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
Declare two more variables, both Long type, for the row and column of the value if it is
Dim varFindRow As Long, varFindColumn As Long