PDA

View Full Version : Find Function



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

CharlesH
06-18-2011, 04:42 PM
blurg82,

Welcome too the forum.

Would it be possible for you too attach a copy of your workbook so we can see a sample of what your working with.

blurg82
06-18-2011, 04:59 PM
Hi Charles,

I can't post the exact file I am working with because of confidentiality issues, but here is a quick and ugly sample of what I have done so far.

Thanks for taking the time,

Eric

6158

CharlesH
06-18-2011, 05:12 PM
Hi,

I'll look at your workbook, but what is the end result. For each find are you wanting to update something?
Or, when the "Last Find" you want it too go back to "C14"?
Also you can set up a "Next" and "Previous" command so you can go to the next (same item) and when you reach the end it can give you a message no more records. You can also count the number of "Matched" item and some place on the form give you the count.

mikerickson
06-18-2011, 05:20 PM
When I run the routine, it cycles through all occurences in the search range.
There are only two problems that I see with that routine.
1) If a cell outside of the rows 14:125 is Active when the Find button is pressed, there is an error.

2) If all occurrences of the search term are marked EOS, the code becomes an infinite loop.

blurg82
06-18-2011, 05:41 PM
Hmm - you are right, it does cycle through and start again! It wasn't doing that for me earlier ...

The end goal is ultimately to simply select the cell where the unit that is being searched is found. On my original sheet, is unloads then reloads the userform to populate it with data from the worksheet.

The first problem: Would it make sense to preface it with a?

If Not Intersect(ActiveCell, Worksheets("Ottawa Roster").Range("C14:N125")) Is Nothing Then

The second problem: How can I reword that so that it ignores any EOS units and moves on to look only for ones that don't have that EOS in the previous column?

nameltrab
06-18-2011, 06:50 PM
So, I've added the following to force the active cell within the proper range before searching (and I moved the "Search:" heading):

Runs the code if the activecell is in the proper range:



Private Sub Search_Unit_Click()

Search:

If Not Intersect(ActiveCell, Worksheets("Ottawa Roster").Range("C14:N125")) Is Nothing Then 'run the search code



If the cell is not in the proper range:



If Intersect(ActiveCell, Worksheets("Ottawa Roster").Range("C14:N125")) Is Nothing Then
Worksheets("Ottawa Roster").Range("C15").Select 'force activecell to C15
GoTo Search 'go back and try again
End If

End Sub



That seems to have fixed the first problem I think - the runaway loop for the EOS still eludes me ...

mikerickson
06-18-2011, 07:27 PM
To test that the active cell is in the right range, I would use something like this at the beginning
If ActiveCell.Row < 14 Or 125 < ActiveCell.Row Then Exit Sub

To avoid the infinite loop, I'd try something like

Dim minFoundRow as Long

minFoundRow = 100000

'...
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
'...
End If
'...

blurg82
06-18-2011, 08:06 PM
That worked well, it stoped the infinte loop. I've modified it a little bit to pop up a message box if all units are EOS. The only thing is that themessage box pops up the second time you click Find. The first time you click Find is selects the first instance of the search criteria, the second time you click Find, the message box pops up.

How can I adjust this to get the message box on the first click (and not select the active cell.



'If unit is End of Shift - Ignore and continue search
ElseIf Worksheets("Ottawa Roster").Range("B" & ActiveCell.Row) = "EOS" Then
If UnitFound.Row = minFoundRow Then Exit Sub
If UnitFound.Row < minFoundRow Then minFoundRow = UnitFound.Row
MsgBox Unit_Textbox.Text & " is listed End of Shift", vbOKOnly + vbInformation, Unit_Textbox.Text & " Listed EOS"

blurg82
06-18-2011, 08:15 PM
I lied!

After further testing - with that code - it goes to and selects the first instance of a unit with EOS and selects it, then when I click Find again, I get the MsgBox popup and can go no further.