Error Check within Find - Excel VBA

[dar149]

I am searching for a specific text item, but if the item is not found I receive error 91. I have tried unsuccessfully to put in error checking before or after the find, but it still gets a runtime error when the item is not found. Following is the error checking code and the find statement. Any suggestions??

'Check for likely Automation errors.
If Err.Number = 91 Then
' Tell user what happened. Then clear the Err object.
msg = "Error Message!"
MsgBox msg, , "No Match"
Err.Clear ' Clear Err object fields
End If

Cells.Find(What:=compval, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

Once the message box is displayed, when the user clicks OK, I want to go to a specific part of the macro.

Any suggestions would be appreciated...

Debbie

 

[SkipVought]

Don't do a .Activate. Set a range object instead. Then test the range object for Nothing.

Set MyRange = Cells.Find(What:=compval, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)

If MyRange Is Nothing Then
'error trap stuff
End If

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dar149]

This worked... Thanks for the information!

Debbie