Error trap with object variable problem

[Mike H]

Hello, I have a macro with a series of different error traps which are set
at appropriate points in a loop. The errors I want to trap are when the Find
method doesn't find what it's looking for. The code I use looks like this:

' Find the Oracle RefNo & Assignment from the EmpNo sheet
On Error GoTo ErrorTrap
EmpNoRow =
EmpNoSheet.Columns("C:C").Find(What:=InDataSheet.Cells(InDataRow, 3)).Row

there is a value in the specified cell, and all of the object variables are
appropriately set. The code works fine when the value is found, but when it
is missing it displays the Debug dialog box (which is exacly what I want to
trap) .

It says: Run time Error 91 - Object variable or with block not set.

Strangely though, sometimes my error traps do work with identical code. Any
ideas?

Thanks

 

[Colo]

Hello Mike H,

Please use a range type variable instead of EmpNoRow. It make
trappling easy.
When the value is found, a variable rngFound has the range, if th
value is not found a variable rngFound has Nothing.


Code
-------------------

Dim rngFound As Range
Set rngFound = EmpNoSheet.Columns("C:C").Find(What:=InDataSheet.Cells(InDataRow, 3))

If rngFound Is Nothing Then
'NOT FOUND
GoTo ErrorTrap ' As you like
Else
'FOUND
EmpNoRow = rngFound.Row
End If