The code below searches column B looking for what ever value "contents" is. If it finds it all is ok but if it does not it errors. Run time error 91

If the macro can't find the value of "contents" then I would like it to call another sub routine but I'm at a loss as to how to do this.

Can you help?

thankyou
smiler44



Sub Macro1()
Columns("B:B").Select
Selection.Find(What:=contents, After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

End Sub

I don't work within Excel that much, but presumably this would work the same as in Access:

http://www.baldyweb.com/ErrorTrap.htm

Make 91 one of the Case's, and call the other routine from there.

Thanks Paul. All sorted.

FYI: http://www.cpearson.com/excel/ErrorHandling.htm

Happy to help.

Paul's has given you the right solution to your question but your questions wasn't the right one.

This would normally be done like this:



dim rng as range

set rng = worksheets("Sheetname").Columns("B:B").Find(What:=contents, After:=ActiveCell, LookIn:=xlFormulas, _
Lookat :=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

if rng is nothing then

...do here what you want to do if you don't find what you are looking for

end if


'rng' is a range object which will return the range object of the first match to your search. If it finds no cells it returns nothing which you can test for and then run your code. If you use the find method elsewhere in your code it could return the same error and result in code being run unintentionally.

Chergh,
I see what your saying and I think I would like to use your method but can't get it to work. I get a run timr error 13 Type mismatch. Try as I have i've not found the solution. smiler44