Range.Delete causing object error 424

[banaanas]

Hi!

I am trying to create a macro that checks all cells in range for a match, and if found will delete a part of the range.
Everything works fine until my code deletes the first cells of the range, then on the next loop, it crashes with runtime error 424 objecr required.

This has to have something to do with the original range changing right? Is there a way how to continue my loop?

Please Login or Register  to view this content.

[Kaper]

Hi,

Indeed, main problem is that after first delete, the cell assigned to FoundOne range variable is deleted (it was first cell found with Find, so it's first meeting criteria in

Please Login or Register  to view this content.

loop)
so next call to:

Please Login or Register  to view this content.

generates an error. because VBA cannot evaluate FoundOne.value (as FoundOne has been already deleted)

One possibility is to assign "just found" FoundOne.value to a variable, and then compare cell.value with that variable. Or (shall be quicker) just repeat Find, not look for each cell to compare with Find result, like :

Please Login or Register  to view this content.

PS. It's not necessary, but makes reading easier if you declare all variables. for instance in the code

Please Login or Register  to view this content.

declarations would make reading easier, and (may be it is declared somewhere public, because otherwise would cause an error , lastrow has no assigned value