Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Macro to Find and Mark | New Users to Excel | |||
looping to End | Excel Programming | |||
Looping | Excel Programming | |||
Looping | Excel Programming | |||
Looping Find | Excel Programming |