Have the Find, desperately need the FindNext !!

[Jeanette]

All credit to JLatham for the code below - A BIG THANKYOU! I now have
a UserForm with two text boxes, a "Search" Button and "Cancel"
Button. It works perfectly for one occurrence, but can someone please
help me by adding what I need in order to find the NEXT occurence each
time the user clicks the "Search" button? (I am searching for the
contents of a cell within a range that is NOT red & strikethrough). I
would soooooo appreciate the help! Thanks in advance. Here's the
code:

Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String

'get the surname entry
'remove leading/trailing whitespace
'and convert to UPPERCASE for tests
findEntry = UCase(Trim(Me.TextBox1))
If findEntry <> "" Then
'we do have a last name to seek
'look in column B for it
Set searchList = ActiveSheet.Range("B1:" & _
ActiveSheet.Range("B" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex <> whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
'if we get here, no match on lastname
'was found, see if we have a first name
'to look for
findEntry = UCase(Trim(Me.TextBox1))
If findEntry <> "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex <> whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
Else
'did not have last name, do we have
'a first name to search for
findEntry = UCase(Trim(Me.TextBox2))
If findEntry <> "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex <> whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
End If

 

[Don Guillett]

Without looking at this, have you tried FINDNEXT. Look in the vba help index

 

[Jeanette]

Without looking at this, have you tried FINDNEXT. Look in the vba help index

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -

Thanks Don, yes I have looked at FINDNEXT in the vba help, but I don't
know where or how to tie it into the code I have!!! Perhaps someone
can help?