Re: Why does this not stop when I push "yes' or "cancel in the mes

Hello from Steved

I type in "061,3.40"

I want it to stop at the first one but when I push cancel it carries on to
the next one.

In this case their are only 2 instances.

i Thankyou.

"Dave Peterson" wrote:

That's what I meant.

What happened when you ran it and clicked cancel?



Steved wrote:

Hello from Steved

I must admit somebody kindly put the macro together for me
meaning I'm a little lost.

from This

Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String

to This,

Dim res As Variant, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String

Is the above what you mean if not please help me , Thankyou.

"Dave Peterson" wrote:

It stopped for me, but I'd declare res as a variant.

Dim res As Variant

If I declared res as a string, and clicked cancel, then res was actually "False"
(the string), not False (the boolean.

I'm not sure what pushing the Yes means, though.

Steved wrote:

Hello from Steved

Please why does this not stop when I push "yes' or "cancel in the message box.

I can have up to 4 of the same occurences, for example I might what it to
carry on after finding the first instance and stop it on the 3rd.

I thankyou.

Sub Schoolfind()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = Active***.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
MsgBox "Choose One", vbYesNoCancel, "Three Options."
' 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

--

Dave Peterson


--

Dave Peterson

.

Loading