Find and FindNext

[abenitez77]

When I use Find and FindNext, it is looping around the same 2 values it finds.

This is the code I use to search for "X" and when I use FindNext to keep searching, it finds the second "X". Then on the next FindNext it comes back to the first "X" I found. How do I make it stop at the last one that exists?


'Search for "X" markers **************************************************************************************
mySearch5 = "X"
Set iSheetE = Range("A44:O54").Find(What:=mySearch5, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not iSheetE Is Nothing Then
strBanner = iSheetE.Offset(0, 1).Value
strBannerCode = iSheetE.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

'Search Next X
Set iSheetE2 = .FindNext(iSheetE)
If Not iSheetE2 Is Nothing Then
strBanner2 = iSheetE2.Offset(0, 1).Value
strBannerCode2 = iSheetE2.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

'Search Next X
Set iSheetE3 = .FindNext(iSheetE2)
If Not iSheetE3 Is Nothing Then
strBanner3 = iSheetE3.Offset(0, 1).Value
strBannerCode3 = iSheetE3.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

Set iSheetE4 = FindNext(iSheetE3)
If Not iSheetE4 Is Nothing Then
strBanner4 = iSheetE4.Offset(0, 1).Value
strBannerCode4 = iSheetE.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

Set iSheetE5 = .FindNext(iSheetE4)
If Not iSheetE5 Is Nothing Then
strBanner5 = iSheetE5.Offset(0, 1).Value
strBannerCode5 = iSheetE5.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

....

 

[shg]

You need to put the address of the first cell found find in a string variable, and test that in each loop iteration. When you find it again, you're done.

 

[abenitez77]

you mean like this below? Also, this time I only have 2 "X" in my range, but there could be a random number of "X" in my range (no more than about 10).

mySearch5 = "X"
Set iSheetE = Range("A44:O54").Find(What:=mySearch5, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not iSheetE Is Nothing Then
strBanner = iSheetE.Offset(0, 1).Value
strBannerCode = iSheetE.Offset(0, 2).Value
myaddr = iSheetE.Address
Else
GoTo NoMoreX
End If

'Search Next X
Set iSheetE2 = .FindNext(myAddr)
If Not iSheetE2 Is Nothing Then
strBanner2 = iSheetE2.Offset(0, 1).Value
strBannerCode2 = iSheetE2.Offset(0, 2).Value
Else
GoTo NoMoreX
End If

 

[shg]

Sub ab()
  Dim rFind         As Range
  Dim sAddr         As String

  With Range("A44:O54")
    Set rFind = .Find(What:="X", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not rFind Is Nothing Then
      sAddr = rFind.Address
      Do
        ' anything that doesn't clobber the value in rFind
        Set rFind = .FindNext(rFind)
      Loop While rFind.Address <> sAddr
    End If
  End With
End Sub

 

[abenitez77]

Excellent! Thank you.

 

[shg]

You're welcome.

I'm more likely to respond to your next question if you put your code in CODE tags.

your code here