Help with 'Find Loops'

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
268
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.

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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try it this way:
Code:
Sub Macro1()
Dim R As Range
Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = 1
Else
    Exit Do
End If
Loop

Do
Set R = Cells.Find(What:="LOOKING", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = 2
Else
    Exit Do
End If
Loop
'Repeat for rest of finds
End Sub
 
Upvote 0
Try it this way:
Code:
Sub Macro1()
Dim R As Range
Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = 1
Else
    Exit Do
End If
Loop

Do
Set R = Cells.Find(What:="LOOKING", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = 2
Else
    Exit Do
End If
Loop
'Repeat for rest of finds
End Sub

JoeMo - Thank You so much ~

My next challenge is to not have to write the codes that changes the original text string so the code doesn't loop continuously. Currently the code works like this:

Code:
Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = "Foo1"
Else
    Exit Do
End If
Loop

Do
Set R = Cells.Find(What:="Foo1", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Value = "OTHER"
Else
    Exit Do
End If
Loop

So, first before I even begin the journey to eliminate the code that resets the original text string and just have each Loop go through the current region once before moving on to the next Loop - Can this even be done? If so, can you point me in the right direction on things to try so I can perhaps figure this out for myself.

Thank You ~
 
Upvote 0
JoeMo - Thank You so much ~

My next challenge is to not have to write the codes that changes the original text string so the code doesn't loop continuously. Currently the code works like this:

Code:
Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    R.Value = "Foo1"
Else
    Exit Do
End If
Loop

Do
Set R = Cells.Find(What:="Foo1", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Value = "OTHER"
Else
    Exit Do
End If
Loop

So, first before I even begin the journey to eliminate the code that resets the original text string and just have each Loop go through the current region once before moving on to the next Loop - Can this even be done? If so, can you point me in the right direction on things to try so I can perhaps figure this out for myself.

Thank You ~
After the first pass through the loop, if R is not nothing then capture the cell address of R - say fAdr = R.Address but don't change the cell contents. Then on the next pass, if R.address = fAdr your back to the same cell so Exit Do and move on.
 
Upvote 0
I created these find loops to simply locate each occurance of a specific text string in the current region
A couple of questions...

1) You said "current region", but your Find code does not look like it is restricting itself to the current region around the active cell unless the current region IS the worksheet's used range. So, where can the words you want to find be located... in a specific region of the data or anywhere in the data?

2) Within whatever region you are search... will any of the cells contain formulas?
 
Upvote 0
After the first pass through the loop, if R is not nothing then capture the cell address of R - say fAdr = R.Address but don't change the cell contents. Then on the next pass, if R.address = fAdr your back to the same cell so Exit Do and move on.

Joe - Thank You again!

I worked your idea inside and outside the Loop, inside and outside the If statement and both at the beginning and end of the Loop - I tried to Dim the variable as well. In some cases Excel just locked up and in other cases the Loop would run continuously; I think because the way I set it up inside the Loop (obviously incorrect) the fAdr would simply update with each pass.

I understand the context of the code but just can't seem to get it to work. Can you supply another hint

Below is the last iteration I attempted and it returns a RunTime 91 Error

Code:
Dim fAdr As Range

Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    fAdr = R.Address
Else
    Exit Do
End If

If R.Address = fAdr Then
    Exit Do
End If

Loop
 
Upvote 0
Hello Rick ~

Thank You for chiming in as well

My use of 'Current Region' was perhaps a bit lazy on m behalf - I was using Current Region as a search parameter in a previous iteration but have since gone back to just using 'Cells'

As far as where the words can be found - They can be found anywhere from Column D to Column T and in any of the 300 to 10,000 rows of data -

Lastly, to your formula question - No, no formulas exist anywhere in the data I'm searching

I apologize for the delayed response -

Thank You for any insights you can providel
 
Upvote 0
Joe - Thank You again!

I worked your idea inside and outside the Loop, inside and outside the If statement and both at the beginning and end of the Loop - I tried to Dim the variable as well. In some cases Excel just locked up and in other cases the Loop would run continuously; I think because the way I set it up inside the Loop (obviously incorrect) the fAdr would simply update with each pass.

I understand the context of the code but just can't seem to get it to work. Can you supply another hint

Below is the last iteration I attempted and it returns a RunTime 91 Error

Code:
Dim fAdr As Range

Do
Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not R Is Nothing Then
    R.Offset(0, 1).Resize(, 25) = ""
    fAdr = R.Address
Else
    Exit Do
End If

If R.Address = fAdr Then
    Exit Do
End If

Loop
Maybe like this:
Code:
Dim fAdr As String

Do
    Set R = Cells.Find(What:="OTHER", After:=ActiveCell, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not R Is Nothing Then
        If R.Address = fAdr Then Exit Do
        R.Offset(0, 1).Resize(, 25) = ""
        fAdr = R.Address
    End If
Loop
Note that fAdr is dimensioned as a string not a range.
 
Upvote 0
Hello Rick ~

My use of 'Current Region' was perhaps a bit lazy on m behalf - I was using Current Region as a search parameter in a previous iteration but have since gone back to just using 'Cells'

As far as where the words can be found - They can be found anywhere from Column D to Column T and in any of the 300 to 10,000 rows of data -

Lastly, to your formula question - No, no formulas exist anywhere in the data I'm searching

See if this code works for you...
Code:
Sub ClearNextCell()
  Dim V As Variant
  For Each V In Split("[B]OTHER,LOOKING,REFUSED,AVAILABLE,CLINICS,TRANSFER[/B]", ",")
    Columns("D:T").Replace V, "=" & V, xlWhole, , False
  Next
  Columns("D:T").SpecialCells(xlFormulas).Offset(, 1).ClearContents
  Columns("D:T").Replace "=", "", xlPart
End Sub
The above is the complete macro for all your words (see the comma delimited list in case you want to add or remove words).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top