Looping Find Next
 

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

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

.

Looping Find Next
 

Hi Helen

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

Cheers
Sanj


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

.

.

Looping Find Next
 

Thanks for you help Duncan, you've been a great help!


-----Original Message-----
Could you not just use Selection.Replace instead of
Selection.Find?

But to loop that I would suggest using the error to end
the loop, something
like the following will do the trick.

Duncan
http://excel.duncan-fiona.co.uk

Sub FindLoop()
Dim boErr As Boolean

On Error GoTo oops

boErr = False

Do
Selection.Find(What:="2", After:=ActiveCell,
LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
MatchCase:= _
False).Select
If boErr = True Then Exit Do
Selection.ClearContents
Loop

Exit Sub

oops:
If Err.Number = 91 Then boErr = True: Resume Next
End Sub

" wrote:

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


.