Looping Find Next

G

Guest

Hi All

I have the following code which finds the #DIV/0! error
and removes this value from the sheet that I'm working
on, the code is below but I was wondering how I could
loop this rather than run the same macro continuously
until I get an error?

The code I've got is as follows:

Selection.Find(What:="#DIV/0!", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Select
Selection.ClearContents
Cells.FindNext(After:=ActiveCell).Activate
Selection.ClearContents
 
H

Helen Trim

Put the FindNext in a never-ending loop and use error
handling to jump out when it has finished:

On Error GoTo Jump_Out

Selection.Find(What:="#DIV/0!", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Select
Selection.ClearContents
Do
Cells.FindNext(After:=ActiveCell).Activate
Selection.ClearContents
Loop Until False

Jump_Out:
Exit Sub


HTH
Helen
 
S

Sanj

Hi Helen

That piece of code worked an absolute treat. Thank-you
very much for your help!!!

Cheers
Sanj
 
Top