Error trapping using the FIND method???

[Apollo6]

I am running a macro that finds a name on one worksheet then looks at another to find the match, if it exist. I put 'On Error GoTo ERR_MISSING_RECORD' to allow the code to continue if a match wasn't found. It works on the first try when it doesn't find a match. However, when it loops through and doesn't find the next name it gives me "Object variable or With block variable not set" error??? The code is below that I'm using. Am I using the FIND method incorrectly?


Do While ActiveCell.Value <> &quot;&quot;
strEmployee = ActiveCell.Value
Sheets(strOldMonth).Activate
Range(&quot;B1&quot.Select
On Error GoTo ERR_MISSING_RECORD
Cells.Find(What:=strEmployee, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate
lOldBalance = ActiveCell.Offset(0, 13).Value
ERR_MISSING_RECORD:
lOldBalance = 0

Sheets(strNewMonth).Activate
ActiveCell.Offset(0, 10).Value = lOldBalance
lNewBalance = ActiveCell.Offset(0, 9).Value + ActiveCell.Offset(0, 10).Value - ActiveCell.Offset(0, 11).Value - ActiveCell.Offset(0, 12).Value
ActiveCell.Offset(0, 13).Value = lNewBalance
ActiveCell.Offset(1, 0).Select
lOldBalance = 0
lNewBalance = 0
Loop

 

[RobBroekhuis]

The on error goto statement should divert the code to an error handler routine, which will always include a &quot;resume&quot; statement. Without the resume statement, the error itself is not cleared, and will pop up again in unexpected places.
In your case, the error handler might be:

...
exit sub 'normal code execution shouldn't go into this part
' of your sub
NotFound:
resume AfterFindLoop
end sub

where AfterFindLoop is the label to which you want the code to jump. There are other solutions to your problem as well, this is just one.
Rob