This code in this form does absolutely nothing. It was supplied when you
requested a way to use a message box with three options. It is written as
psuedo code which means you have to revise it by adding in meaningful
informatrion. See the remarks following the code.

myOpt = MsgBox ("Choose One", vbYesNoCancel, "Three Options.")
If myOpt = vbYes Then
'Do one thing
ElseIf myOpt = vbNo Then
'Do Another
ElseIf myOpt = vbCancel
'Do Something else
Else
'Do Nothing
End If

Where it says, "Choose one" there should be a question that the user can
respond to with a "Yes", "No" or "Camcel".

Then based on the user's response, where there are commented remarks like,

'Do one thing

you would enter code that executes to continue the process or exit the
process or some other option. The same applies to 'Do Another and 'Do
something else. These are just filler statements to illustrate how to use
the message box options, they are not actual code. Since I have no idea what
you are trying to do, you will have to put in the code that meets your needs,
or as Dave suggested, give a better explanation of what you are trying to do,
and a description of where the data resides in the sheet or sheets layout. I
know it is frustrating. Been there, done that!



"Steved" wrote:

Hello JLWhiz from Steved

The below is highliting "Loop While"

I know it's simple but my brain is not thinking straight would you do the
honours please and have a look at what I've done wrong and correct my mistake

The objective of below is to goto the first instance in this case "061,3.40"
if found give me the option off stopping or allowing to go onto the next
"061,3.40"
I Thankyou for your timeout on my issue.


Sub Schoolfind()
Dim res As Variant, 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
myOpt = MsgBox("Choose One", vbYesNoCancel, "Three Options.")
If myOpt = vbYes Then
'Do one thing
ElseIf myOpt = vbNo Then
'Do Another
ElseIf myOpt = vbCancel Then
'Do Something else
Else
'Do Nothing
End If
Exit Do

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



"JLGWhiz" wrote:

Hi Steved. I believe this is the line you are concerned with.

MsgBox "Choose One", vbYesNoCancel, "Three Options."

To use it for a three option control, you need to use a variable like this:

myOpt = MsgBox ("Choose One", vbYesNoCancel, "Three Options.")
If myOpt = vbYes Then
'Do one thing
ElseIf myOpt = vbNo Then
'Do Another
ElseIf myOpt = vbCancel
'Do Something else
Else
'Do Nothing
End If




"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 = 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
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