Error 91

[Jim]

I am using a piece of vba code to 'Find' a cell containing a string.
If the string is Not Found the code returns an error 91 - Object Variable or
With Block Variable not set which I am displaying in a msgbox
Is this the correct Error for a failure to find a string ?

 

[Otto Moehrbach]

Yes. HTH Otto

 

[Jim]

Thanks Otto, that has set my mind at rest
Is there an Excel dialog box that I can use instead of a message box ?

Regards & TIA

 

[Guest]

What do you mean dialog box? What are you trying to accomplish with the find
and then the subsequent failure or success?
Ben

 

[Jim]

I am using a piece of vba code to 'Find' a cell containing a string.
If the string is Found then I am updating a UserForm
If the string is Not Found I am displaying a Message box

Instead of the Message Box (above) I would like to display the built in
dialog that Excel displays, when the data is not found, when doing the Find
manually

 

[Guest]

do you mean the find dialog box or the standard error box? I would Strongly
NOT reccomend showing the standard error box to a user that can create major
headaches, you could use this statement to analyze found or not found

dim ran as range
set ran = activesheet.cells.find(what:="mysearchdata")
if ran is nothing then
'not found
else
'found
end if


of course changing the criteria of searching to suit your needs

Ben

 

[Jim]

I mean the error box that is displayed when a manual find fails
"Microsoft Office Excel cannot find the data you're searching for" with an
'OK' button

 

[Chip Pearson]

You can't access that dialog directly. Just use a MsgBox with the
same wording.

 

[Jim]

Thanks Chip - I was begining to come to that conclusion