Run-time error '91' when 'On error goto' and cells.find

Hi all.

I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:

'Run-time error '91':
Object variable or With block variable not set'

Do I have to clear a buffer after each cells.find search?

My code:

Please Login or Register  to view this content.

Thanks in advance,


Bjoern

First, you may want to insert Exit Sub here

Please Login or Register  to view this content.

, unless you want to search for Car after finding Boat.

You should also put

Please Login or Register  to view this content.

at the top of each of the error handling routines. Without being cleared, Excel thinks the error handling is still being done and any new errors will crash the routine.

In general though, using error code like that is resulting in something approaching spaghetti code. A loop that avoids selecting and tests for whether the terms are found is possible.

Please Login or Register  to view this content.

That routine assumes that you want as many as possible of the search terms dealt with. If you want only the first found term handled, ingnoring the others, put

Please Login or Register  to view this content.

after the .Copy line.

Originally Posted by mikerickson

First, you may want to insert Exit Sub here

Please Login or Register  to view this content.

, unless you want to search for Car after finding Boat.

You should also put

Please Login or Register  to view this content.

at the top of each of the error handling routines. Without being cleared, Excel thinks the error handling is still being done and any new errors will crash the routine.

In general though, using error code like that is resulting in something approaching spaghetti code. A loop that avoids selecting and tests for whether the terms are found is possible.

Please Login or Register  to view this content.

That routine assumes that you want as many as possible of the search terms dealt with. If you want only the first found term handled, ingnoring the others, put

Please Login or Register  to view this content.

after the .Copy line.

Thanks for the reply Mike. The routine works like a charm, however, instead of displaying error messages, I have to paste a '0' into the cell on the 'Main' sheet if the searchTerm on the 'Data" sheet isn't present.

I will see if I can tweak this routine, in the meantime, I will close out this post.


Thanks for your help,

Bjoern

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Oops. Add one dot

Please Login or Register  to view this content.

Just change the arguments of the .Offset