Find, offset and clear loop bombs out

Hi numbnuts

First address is a String so I believe it should be written

Please Login or Register  to view this content.

or perhaps

Please Login or Register  to view this content.

Hi numbnuts

The Code is incomplete

Please Login or Register  to view this content.

@ John,

The Is operator is reserved to object variables.

@ nn,

Here's some general information on Range.Find

Basic Range.Find Method

You should explicitly set LookIn, LookAt, SearchOrder (if you care), and MatchByte on each invocation (or the initial invocation in a straight-line section of code) of the Find method, as all persist each time Find is invoked via VBA or Find is invoked from the user interface. (MatchCase and SearchFormat persist from invocation to invocation in the UI, but not in VBA).

Please Login or Register  to view this content.

The Bad Find Example in Help

(The variable names are changed below to agree with those above)

Please Login or Register  to view this content.

That Loop While line should NEVER be used. If rFind Is Nothing, then rFind.Address will error; and if rFind has an Address, then it is not Nothing. (VBA always evaluates all arguments to a logical expression; if it paid attention to the And operator and quit after evaluating the first, it wouldn't be a problem.)

If the search ultimately ends because you are destroying (as in the Help example above) or hiding the found value (as in the example below), then use

Please Login or Register  to view this content.

If it ends because you are finding the first entry again, then you should use

Please Login or Register  to view this content.

If either could happen then

Please Login or Register  to view this content.