Why does this not stop when I push "yes' or "cancel in the message

[Steved]

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]

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.

 

[Dave Peterson]

ps. Maybe it's a difference in language?

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]

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.

 

[Steved]

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]

That's what I meant.

What happened when you ran it and clicked cancel?

 

[JLGWhiz]

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]

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]

ahhh.

That makes much more sense.

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]

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

 

[Dave Peterson]

First, it's difficult to guess what you really want just by looking at your
code. But my guess is that you want to find the first row that has a match in
column C and a match in column D for the numbers you input into that inputbox.

Then if one is found, you want to be prompted to look for the next or just stay
where you are.

==================================
An aside...

Personally, I wouldn't use a macro for this. I'd just apply
data|filter|autofilter to the range and filter column C to show all the values
that I want to see and then filter column D to see the same thing.

I could see all the entries at once and not have to be bothered with the macro
-- that always starts at the top!
==================================

But if you want....

Here are my assumptions.

The stuff in column C is text--not numbers formatted to show leading 0's.
'001 not 1 with a format of "001"

But the stuff in column D is really a number but it's formatted to show 2
decimals.

But these are just guesses based on the use of .Text in your code.

If that's true, then I'm going to count the number of rows that match the stuff
entered into that inputbox using a worksheet function.

I could use something like this in a cell:
=sumproduct(--(c1:c99="001"),--(d1:d99)=8))

If my assumptions are wrong, then the quotes will be screwed up. And the code
will have to change.

But to correct the code, you'll have to share what the data really is -- and
you'll have to make sure that all the data is what you say it is. No 99%
accuracy. It has to be 100% consistent.

Anyway...

If you want to try:

Option Explicit
Sub Schoolfind()

Dim res As String
Dim myOpt As Long

Dim RngToSearch As Range
Dim RngFound As Range

Dim FirstValue As String
Dim SecondValue As String

Dim v As Variant
Dim wks As Worksheet
Dim HowManyMatches As Long 'total records with matches
Dim fCtr As Long 'counter of these found matches
Dim iCtr As Long 'just a counter for anything
Dim KeepLooking As Boolean

Set wks = ActiveSheet

With wks
Set RngToSearch = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

'no need for application.inputbox and type:=2
res = InputBox(Prompt:="Type School Number as 001,8.00 to find the " _
& "school you are looking for", Title:="Find School", _
Default:="001,8.00")

'remove all spaces!
res = Replace(res, " ", "")
If res = "" Then
Exit Sub 'exit if no entry and OK is clicked
End If

v = Split(res, ",")
If UBound(v) - LBound(v) + 1 <> 2 Then
MsgBox "Please enter exactly two criterial!"
Exit Sub
End If

For iCtr = LBound(v) To UBound(v)
If IsNumeric(v(iCtr)) = False Then
MsgBox "Both criteria must be numeric!"
Exit Sub
End If
Next iCtr

FirstValue = v(LBound(v))
SecondValue = v(UBound(v))

'one way to find out how many matches are in the worksheet is
'to use a formula like:
'=sumproduct(--(c1:c99="001"),--(d1:d99)=8))
'it would look like this in code:
HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & Chr(34) & FirstValue & Chr(34) & "),"
_
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")

'just for testing!
MsgBox HowManyMatches & " rows with matches!"

If HowManyMatches = 0 Then
MsgBox "Nothing found with both columns matching!"
Exit Sub
End If

With RngToSearch
Set RngFound = .Cells.Find(What:=FirstValue, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
Searchorder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

KeepLooking = True
fCtr = 0
Do
If RngFound.Offset(0, 1).Value <> CDbl(SecondValue) Then
'do nothing special--keep looking
Else
'found a match
fCtr = fCtr + 1
Application.Goto Reference:=RngFound.Offset(0, -1)
If fCtr = HowManyMatches Then
MsgBox Prompt:="This is the last one!", _
Title:="On: " & fCtr & " of " & HowManyMatches
KeepLooking = False
Exit Do
Else
myOpt = MsgBox(Prompt:="Keep looking?", _
Buttons:=vbYesNo, _
Title:="On: " & fCtr & " of " & HowManyMatches)
If myOpt = vbNo Then
'stop looking
KeepLooking = False
Exit Do
End If
End If
End If

If KeepLooking = True Then
Set RngFound = RngToSearch.FindNext(RngFound)
End If
Loop

End Sub

 

[JLGWhiz]

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]

Hello from Steved

to look for 001,3.25

"001" is a number and "3.25" is a decimal time.


The below finds everything in C:C as +0 will treat it as a number.

=SUMPRODUCT(--(C1:C1000="001"+0))

The below finds everything in D as +0 will treat it as a number.

=SUMPRODUCT(--(D11000="3.25"+0))

ok what are my objectives

1 find 001 in column C:C

2 if 001 found in Column C:C then find 3.25 in D

Result is in Row 17 we have 001,3.25

3 move to the Column B:B in this case "B17"

4 in Column B:B I have Data That's need to be updated "B17"

Is it possible please to have a message box do the above.

I know gentlemen I'm a challenge but hopefully I've made this exercise more
clearer.

ps by putting in "+0" it finds every instance


I Thankyou.

 

[Dave Peterson]

If both are numbers, then the formula that you'd use in a worksheet cell would
look like:

=sumproduct(--(c1:c99=1),--(d1:d99)=3.25))

The double quotes aren't necessary and would make the results incorrect.

So try changing this line:

HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & Chr(34) & FirstValue & Chr(34) & ")," _
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")

to

HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & FirstValue & ")," _
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")

 

[Steved]

Hello Dave from Steved

This is what I needed and I thankyou.

Dave the reason I do not use filters is that this file is on a network and
is live 24/7, so that others did'nt get confused, hence the reason for your
code.

Once again I thankyou for your patience

Have a great day.