Help to repeat find on button click

[Jeanette]

Newbie and need help - assume zero knowledge! JLatham helped me with
this code, but don't know how to repeat it's function at
CommandButton3. Have read the VBA help and still none the wiser!
Would be very grateful if one of you guys would wave your magic
wands! Here's the code:
A BIG THANKYOU IN ADVANCE
Jeanette

Private Sub CommandButton1_Click()
'the "search" button will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see if there's an entry in first name
box and if there is, will search for match

CommandButton1.Visible = True

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
CommandButton1.Visible = False
CommandButton3.Visible = True
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
CommandButton1.Visible = False
CommandButton3.Visible = True
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
CommandButton1.Visible = False
CommandButton3.Visible = True
Exit Sub ' all finished
End If
End If
Next
End If
End If
End Sub
Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub
Private Sub CommandButton3_Click()
'Do exactly the same again, but start it from the active cell
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub

 

[Simon Lloyd]

What do you mean repeat its function?, can you explain a little better
as a simple click of button 1 would run it again.

Newbie and need help - assume zero knowledge! JLatham helped me with
this code, but don't know how to repeat it's function at
CommandButton3. Have read the VBA help and still none the wiser!
Would be very grateful if one of you guys would wave your magic
wands! Here's the code:
A BIG THANKYOU IN ADVANCE
Jeanette
Code:
--------------------
Private Sub CommandButton1_Click()
'the "search" button will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see if there's an entry in first name
box and if there is, will search for match

CommandButton1.Visible = True

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
CommandButton1.Visible = False
CommandButton3.Visible = True
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
CommandButton1.Visible = False
CommandButton3.Visible = True
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
CommandButton1.Visible = False
CommandButton3.Visible = True
Exit Sub ' all finished
End If
End If
Next
End If
End If
End Sub

Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub

Private Sub CommandButton3_Click()
'Do exactly the same again, but start it from the active cell
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub
--------------------

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Patrick Molloy]

when you find that you have similar code blocks - in this case two sets for
finding data, then its more efficient to create one find process and call it
twice

re: Next Find
the worksheet find looks at all cells for the text looked for, and when you
get to the 'last' cell, the find algorithm returns to the first cell found
and loops around again.
so in code, when we find the first cell, we note its address, then find the
next repeatedly until we get the address of the first cell again. This is
well explained in Help, plus there's a code sample

dim found as Range
dim source as Range
Set source = range("mydata")
' find first cell
set found = source.Find("what??")
'check something found
If Not found is nothing then
'something found
'so get its address
addr = found.address
'start a loop to handle finds
DO
' do something with the found cell
'find the next
SET found = source.findNext(found)
LOOP while found.address<>addr
Else
msgbox "NO cells found"
End if
End Sub