To find more than one Please

[Steved]

Hello from Steved

I've a spreadsheet that has 2 columns of Data

If I type in 003,3.20 that's fine but sometimes there may be more with the
same data, how can the below macro be modified to find the next and so on.
Thankyou.

Sub FindPart()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox " School Not Found"
End If
End Sub

 

[ben]

VBA help - Search for FindNext, it has a very simple sample there that is
easily modified for your code, I use it a lot.

 

[Toppers]

Steve,
The code will find all occurences if you remove the "Exit
Do" statement. What happens at the GoTo Reference ... do you input data?


Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1) '<=== what
happens here?
Exit Do <====
End If

 

[Tom Ogilvy]

Sub FindPart()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
' commenting out the next line should do it
' Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox " School Not Found"
End If
End Sub

 

[Steved]

Hello Toppers from Steved

I type in 001,3.25 to find the row I require.

001 is in Column C and 3.25 is in Column D.

 

[Steved]

Hello from Steved

I've taken out the line Exit Do.

ok now can I have it stopped at the first occurence please as at the moment
it will find the last, something like If I push the F4 it will then find the
next please.

Thankyou.

 

[Tom Ogilvy]

at the moment it will find the last
No, it is finding all. It just doesn't stop until it finds the last.


There is no easy way to pause code execution will you putter around on the
sheet. What do you actually want to do with each entry that is found?

Do you just want to look at it. Do you want to enter a value.

It is possible you could combine this with a userform to act as an
interface.

What about just filtering the data so only these rows are visible. Then you
could do your puttering and run another macro to open them all back up.

If that is satisfactory, then maybe you would consider using the built in
autofilter capability. If not, then some code could be written.

--
Regards,
Tom Ogilvy

 

[Steved]

Hello Tom

Tom the 001 is a School route number and 8.15 is the time.

Yes just to look at them.

In Column A, I have a number which 4537 which is the driver doing that School
I have 8 Bus Depots that use this information so if that can type in
001,8.15 to goto the first then do something to move to the second and so on.

Your answer to filters, yes I thought off doing that but I felt in my mind
that getting a macro to do the finding would suit the staff looking for the
school better.

Thankyou.




Thankyou.

 

[Tom Ogilvy]

Sub FindPart()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1), True
' commenting out the next line should do it
msgbox "Click to Continue Searching"
' Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox " School Not Found"
End If
End Sub

 

[Steved]

Hello Tom from Steved

Thankyou very much it works brilliant, once again I thankyou for your time.

Cheers.