blurg82
06-18-2011, 04:27 PM
Hi folks,
This search looks through the Range C14:C125. It always starts looking from the row where the activecell is. That way, if it finds one match, pressing the find button again looks for the next match. The problem is, once the search reaches the end of the range (C125) I can't seem to get it to go back up to the top and look again starting at C14.
The ultimate goal is to push the find button, have it find a match and select the cell. Pushing the find button again finds the next one down in the range, and then selects it (if there are more than one instance of the same entry...) and, if while looking it gets to the the bottom of the range, C125, it heads back up to C14 and continues from there.
Let me know if I'm not being clear!!
Thanks again in advance.
Private Sub Search_Unit_Click()
'Check if entry is blank
If Unit_Textbox.Text = vbNullString Then
MsgBox " Please enter a valid 4 digit unit number. ", vbOKOnly + vbCritical, "Enter Unit"
GoTo ProcedureEnd
End If
'Identify where to search
Set SearchRange = Worksheets("Ottawa Roster").Range("C14:C125")
'Search Roster and set UnitFound As Address
Search:
Set UnitFound = SearchRange.Find(What:=(Unit_Textbox.Text), After:=Worksheets("Ottawa Roster").Range("C" & ActiveCell.Row))
'If nothing was found
If UnitFound Is Nothing Then
MsgBox Unit_Textbox & " was not found as an active unit. ", vbOKOnly + vbExclamation, "Unit Not Found"
GoTo ProcedureEnd
'If unit is End of Shift - Ignore and continue search
ElseIf Worksheets("Ottawa Roster").Range("B" & UnitFound.Row) = "EOS" Then
Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select
GoTo Search
'Select an active unit (not end of shift)
Else: Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select
GoTo ProcedureEnd
End If
Unload Roster_Userform
Roster_Userform.Show
ProcedureEnd:
On Error GoTo 0
Exit Sub
End Sub
This search looks through the Range C14:C125. It always starts looking from the row where the activecell is. That way, if it finds one match, pressing the find button again looks for the next match. The problem is, once the search reaches the end of the range (C125) I can't seem to get it to go back up to the top and look again starting at C14.
The ultimate goal is to push the find button, have it find a match and select the cell. Pushing the find button again finds the next one down in the range, and then selects it (if there are more than one instance of the same entry...) and, if while looking it gets to the the bottom of the range, C125, it heads back up to C14 and continues from there.
Let me know if I'm not being clear!!
Thanks again in advance.
Private Sub Search_Unit_Click()
'Check if entry is blank
If Unit_Textbox.Text = vbNullString Then
MsgBox " Please enter a valid 4 digit unit number. ", vbOKOnly + vbCritical, "Enter Unit"
GoTo ProcedureEnd
End If
'Identify where to search
Set SearchRange = Worksheets("Ottawa Roster").Range("C14:C125")
'Search Roster and set UnitFound As Address
Search:
Set UnitFound = SearchRange.Find(What:=(Unit_Textbox.Text), After:=Worksheets("Ottawa Roster").Range("C" & ActiveCell.Row))
'If nothing was found
If UnitFound Is Nothing Then
MsgBox Unit_Textbox & " was not found as an active unit. ", vbOKOnly + vbExclamation, "Unit Not Found"
GoTo ProcedureEnd
'If unit is End of Shift - Ignore and continue search
ElseIf Worksheets("Ottawa Roster").Range("B" & UnitFound.Row) = "EOS" Then
Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select
GoTo Search
'Select an active unit (not end of shift)
Else: Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select
GoTo ProcedureEnd
End If
Unload Roster_Userform
Roster_Userform.Show
ProcedureEnd:
On Error GoTo 0
Exit Sub
End Sub