Hello,
I am having a couple issues with Excel Find Loops.
I created these find loops to simply locate each occurance of a specific text string in the current region then clear the contents of the cells just to the right of the text string.
The first Loop works perfectly; however, each successive Loop produces a RunTime 91 Error; thus seemingly ignoring the error trap.
The second issue I am having is trying to get these loops to go through the current region just once finding each text string, execute the code then move to the next loop.
Right now to accomidate this I have written code to change the text string to a number after the cells to the right have been deleted thus insuring the error when find produces no results.
I am having a couple issues with Excel Find Loops.
I created these find loops to simply locate each occurance of a specific text string in the current region then clear the contents of the cells just to the right of the text string.
The first Loop works perfectly; however, each successive Loop produces a RunTime 91 Error; thus seemingly ignoring the error trap.
The second issue I am having is trying to get these loops to go through the current region just once finding each text string, execute the code then move to the next loop.
Right now to accomidate this I have written code to change the text string to a number after the cells to the right have been deleted thus insuring the error when find produces no results.
Code:
Sub Macro1()
Do
On Error GoTo 1
Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 1
Loop
1:
Do
On Error GoTo 2
Cells.Find(What:="LOOKING", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 2
Loop
2:
Do
On Error GoTo 3
Cells.Find(What:="REFUSED", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 3
Loop
3:
Do
On Error GoTo 4
Cells.Find(What:="AVAILABLE", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 4
Loop
4:
Do
On Error GoTo 5
Cells.Find(What:="CLINICS", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 5
Loop
5:
Do
On Error GoTo 6
Cells.Find(What:="TRANSFER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Resize(, 25) = ""
ActiveCell.Value = 5
Loop
6:
End Sub