Find Function

[blurg82]

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.

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Search_Unit_Click() 
     [COLOR=darkgreen]'Check if entry is blank[/COLOR]
    [COLOR=blue]If[/COLOR] Unit_Textbox.Text = vbNullString [COLOR=blue]Then[/COLOR] 
        MsgBox " Please enter a valid 4 digit unit number. ", vbOKOnly + vbCritical, "Enter Unit" 
        [COLOR=blue]Goto[/COLOR] ProcedureEnd 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
     [COLOR=darkgreen]'Identify where to search[/COLOR]
    [COLOR=blue]Set[/COLOR] SearchRange = Worksheets("Ottawa Roster").Range("C14:C125") 
     
     [COLOR=darkgreen]'Search Roster and set UnitFound As Address[/COLOR]
Search: 
    [COLOR=blue]Set[/COLOR] UnitFound = SearchRange.Find(What:=(Unit_Textbox.Text), After:=Worksheets("Ottawa Roster").Range("C" & ActiveCell.Row)) 
     
     [COLOR=darkgreen]'If nothing was found[/COLOR]
    [COLOR=blue]If[/COLOR] UnitFound [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR] 
        MsgBox Unit_Textbox & " was not found as an active unit. ", vbOKOnly + vbExclamation, "Unit Not Found" 
        [COLOR=blue]Goto[/COLOR] ProcedureEnd 
         
         [COLOR=darkgreen]'If unit is End of Shift - Ignore and continue search[/COLOR]
    [COLOR=blue]ElseIf[/COLOR] Worksheets("Ottawa Roster").Range("B" & UnitFound.Row) = "EOS" [COLOR=blue]Then[/COLOR] 
        Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select 
        [COLOR=blue]Goto[/COLOR] Search 
         
         [COLOR=darkgreen]'Select an active unit (not end of shift)[/COLOR]
    Else: Worksheets("Ottawa Roster").Range("C" & UnitFound.Row).Select 
        [COLOR=blue]Goto[/COLOR] ProcedureEnd 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
     
    Unload Roster_Userform 
    Roster_Userform.Show 
     
ProcedureEnd: 
    [COLOR=blue]On Error Goto[/COLOR] 0 
    Exit [COLOR=blue]Sub[/COLOR] 
     
[COLOR=blue]End Sub[/COLOR]

 

[Ralajer]

Welcome to the board blurg82,

I couldn't reproduce the stopping on the end of range behavior and I'm not sure if it is a Excel version issue, as I don't know your version, or a particular setting. But adding the highlighted code to your sub will force it to check from the top of the search range if it doesn't find anything from the active cell. It will just loop through as many time the find button is clicked if there are any matches.

    Set UnitFound = SearchRange.Find(What:=(Unit_TextBox.Text), After:=.Range("C" & ActiveCell.Row))
                            
[COLOR="Red"][B]    '// if not found from active cell check from top of search range
    If UnitFound Is Nothing Then
        Set UnitFound = SearchRange.Find(What:=(Unit_TextBox.Text), After:=SearchRange(1, 1))
        '// Found the same entry as last. I.E. Only one entry in range
        If UnitFound.Row = ActiveCell.Row Then
            '// Set action
        End If
    End If[/B][/COLOR]
                
     'If nothing was found
    If UnitFound Is Nothing Then

Let me know if you have any questions.

 

[mikerickson]

What was wrong with the solution here
http://www.vbaexpress.com/forum/showthread.php?t=37918

 

[Ralajer]

Looks like this was posted before the one on vbaexpress. So probably nothing was wrong with it.

 

[Jerry Sullivan]

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.

Hello and Welcome,

I mocked this up and did not have the same problem you describe. I tried a couple of variations of where I started the search in relation to the matches and each time the routine was able to go back to the top and find the next match.

Could you describe a specific scenario in which this didn't work as intended? Something like "Unit 1234 is in Cells C16, C100,C110 and when I start at C105 it only finds the first instance in C110 and then stops."

Post Post.

What was wrong with the solution here
http://www.vbaexpress.com/forum/showthread.php?t=37918
<!-- / message -->

.... Mike, Thanks for calling out the unreported cross post.
Looks like we had some duplicated wasted effort on this.

 

[blurg82]

Hey guys thanks for the help - and sorry about the posts on different boards (it has simply happened on several occaisions, I post something and get no replies, so I figured this time I'd throw my net wider to reach more people. I suppose in future I should wait and, if I get no replies on one forum, then try another...)

JS411 - I'm not sure why it wasn't continuing at the top before, but you're right, when I tried it again it started working and was moving through it properly.

The problem I am having with it now is the section where it says if there is EOS in the cell next to it skip it. If all the search results have EOS next to it, it gets stuck in an infinite loop. A solution WAS posted on the other forum by mikerickson, but I cannot get that to work, not sure why ...

Private Sub Search_Unit_Click()
 
Dim minFoundRow As Long
 
minFoundRow = 100000
 
Search:
If Not Intersect(ActiveCell, Worksheets("Ottawa Roster").Range("C14:N125")) Is Nothing Then
'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
    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
 
''''****** This solution was proposed by mikerickson - it stoped the infinite loop - but now gets stuck at the first EOS ******
    'If unit is End of Shift - Ignore and continue search
    ElseIf Worksheets("Ottawa Roster").Range("B" & UnitFound.Row) = "EOS" Then
        If UnitFound.Row = minFoundRow Then Exit Sub
        If UnitFound.Row < minFoundRow Then minFoundRow = UnitFound.Row
    '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 If
If Intersect(ActiveCell, Worksheets("Ottawa Roster").Range("C14:N125")) Is Nothing Then
Worksheets("Ottawa Roster").Range("C15").Select
GoTo Search
End If
 
End Sub

Again I apologize for the duplicated efforts - thanks again for all your help.

Eric

 

[mikerickson]

This is a good explanation of how to cross post.
http://www.excelguru.ca/node/7

 

[blurg82]

Thanks - I appreciate the friendly heads up, the link, and for not being shunned ! (I admit I never thought about it as having 2 different groups of experts working on the same problem with neither knowing the other even exists !)