View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Helen Trim[_5_] Helen Trim[_5_] is offline
external usenet poster
 
Posts: 1
Default Looping Find Next

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



-----Original Message-----
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

.