ExcelBanter - To find more than one Please

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - To find more than one Please (https://www.excelbanter.com/excel-programming/353835-find-more-than-one-please.html)

Steved

To find more than one Please

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

To find more than one Please

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

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?

"Steved" wrote:

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

Toppers

To find more than one Please

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

"Steved" wrote:

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

Tom Ogilvy

To find more than one Please

Steved

To find more than one Please

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.

"Toppers" wrote:

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

"Steved" wrote:

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

Steved

To find more than one Please

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" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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

Tom Ogilvy

To find more than one Please

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" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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

Steved

To find more than one Please

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" wrote:

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" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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

Tom Ogilvy

To find more than one Please

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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

Steved

To find more than one Please

Hello Tom from Steved

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

Cheers.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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

All times are GMT +1. The time now is 07:14 AM.