Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly Help!
The following code should pop up a message box but it is not doing so where
am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly Help!
There is a correction with the original post a small typo but it still is not
working once I fix the typo Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = True Then MsgBox "One or more of the 3 conditions are false" End If End Sub "Dan Thompson" wrote: The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly Help!
The criteria part of the statement is false, therefor, VBA will not execute
the command line. Try this statement: If one = True And two = True And three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly Help!
Also:
If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
Thank you for your input. However the code you wrote here will not work
for what I am trying to do because any of the variables "one" "two" or "three" in the final macro will be true sometimes and other times they can be false. I want to have a conditional statement that will check to see if all are true and if either of the 3 variables are false then execute my code. In your example the condition is depending on the variable "three" to be false when it may be true sometimes. "JLGWhiz" wrote: The criteria part of the statement is false, therefor, VBA will not execute the command line. Try this statement: If one = True And two = True And three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
Hey JLGWhiz
This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
I get all screwed around with the logic tables and have to use trial and
error when I get into testing multiple boolean results. Here is a site that explains where the problem comes from. http://www.rwc.uc.edu/koehler/comath/21.html The If statement has to capture the condition so that the result is true to make the command line execute, even if the result is negative. This means that you can check to see if it Is, or you can check to see if it is Not, but you cannot check to see if it is maybe. So when you use the And or the Or operators in the criteria line of the If statement you need to be aware of what the logic is doing in evaluating the statement. "Dan Thompson" wrote in message ... Hey JLGWhiz This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
Thanks for the advice I fixed the problem and I realize it was to do with the
order of logic in my code. I understand now that however you present your condition in an "IF" statement the "IF" statement always hast to evaluate to be true in order for VBA to execute the code in the "IF" statement. Bob = 42 IF Bob = 42 Then MsgBox "Happy Birthday" (Condition executes because it is TRUE that Bob is 42) Bob =35 IF Not Bob = 42 Then Exit Sub (Condition executes because it is TRUE that Bob is NOT 42) Both If statements execute because they are evaluated to be true Right ?? on the other hand Bob = 42 IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is FALSE and the "IF" statement will only execute code when the "IF" statement returns true) I think I got it :) btw I looked at that link you sent me and it just made my head spin lol too deep for me Dan Thompson "JLGWhiz" wrote: I get all screwed around with the logic tables and have to use trial and error when I get into testing multiple boolean results. Here is a site that explains where the problem comes from. http://www.rwc.uc.edu/koehler/comath/21.html The If statement has to capture the condition so that the result is true to make the command line execute, even if the result is negative. This means that you can check to see if it Is, or you can check to see if it is Not, but you cannot check to see if it is maybe. So when you use the And or the Or operators in the criteria line of the If statement you need to be aware of what the logic is doing in evaluating the statement. "Dan Thompson" wrote in message ... Hey JLGWhiz This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
Here is a different way to track your "true" conditions...
Sub Test() Dim Count As Long Dim TestCondition As String TestCondition = String(3, "X") & " " For Count = 1 To 4 MsgBox "Count #" & Count MsgBox "Number of 'True' conditions is " & _ Len(Replace(TestCondition, " ", "")) Mid(TestCondition, Count) = " " Next Count End Sub -- Rick (MVP - Excel) "Dan Thompson" wrote in message ... Hey JLGWhiz This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
You got the basic part. That is what counts. And it is all in the logic.
"Dan Thompson" wrote in message ... Thanks for the advice I fixed the problem and I realize it was to do with the order of logic in my code. I understand now that however you present your condition in an "IF" statement the "IF" statement always hast to evaluate to be true in order for VBA to execute the code in the "IF" statement. Bob = 42 IF Bob = 42 Then MsgBox "Happy Birthday" (Condition executes because it is TRUE that Bob is 42) Bob =35 IF Not Bob = 42 Then Exit Sub (Condition executes because it is TRUE that Bob is NOT 42) Both If statements execute because they are evaluated to be true Right ?? on the other hand Bob = 42 IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is FALSE and the "IF" statement will only execute code when the "IF" statement returns true) I think I got it :) btw I looked at that link you sent me and it just made my head spin lol too deep for me Dan Thompson "JLGWhiz" wrote: I get all screwed around with the logic tables and have to use trial and error when I get into testing multiple boolean results. Here is a site that explains where the problem comes from. http://www.rwc.uc.edu/koehler/comath/21.html The If statement has to capture the condition so that the result is true to make the command line execute, even if the result is negative. This means that you can check to see if it Is, or you can check to see if it is Not, but you cannot check to see if it is maybe. So when you use the And or the Or operators in the criteria line of the If statement you need to be aware of what the logic is doing in evaluating the statement. "Dan Thompson" wrote in message ... Hey JLGWhiz This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly H
The messages in this thread are so contorted, it is know if you truly
understand. I hope the following helps to fine-tune your understanding. "Dan Thompson" wrote: Bob = 42 IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is FALSE and the "IF" statement will only execute code when the "IF" statement returns true) First, the better way to write a numerical comparison like that is: If Bob<42 Then Exit Sub But arguably, perhaps you are using that as a paradigm for a logical expression using Not. Second, an IF statement consists of a conditional expression between "If" and "Then", a THEN-clause, and an optional ELSE-clause or ELSEIF-clause. If the conditional expression is true, the statements in the THEN-clause are executed. If the conditional expression is false, the statements in the ELSE-clause are executed. You can think of an ELSEIF-clause as an abbreviation for an IF statement in an ELSE-clause. In either case, after executing the statements in the THEN-clause or ELSE-clause, execution continues with the statement following the IF statement, unless you have a GOTO or EXIT statement in the then-clause or else-clause of course. See the VBA help page for "if then else statement". If one And two And three = True Then MsgBox "All of the 3 conditions are true" That syntax works only by accident because "one" and "two" are Boolean variables. Aside: Using variable names like "one", "two" and "three" that have values other than 1, 2 and 3 is poor form and extremely confusing. Get out of that bad habit quickly. Suppose you have variables Joe, Moe and Curly, and you want to test if all three are equal to 42. The following would __not__ do that: If Joe and Moe and Curly = 42 Then Msgbox "all are 42" Test with Joe=40, Moe=41 and Curly=42. You should see that it incorrectly displays "all are 42". The IF statement above tests if Joe is TRUE and Moe is TRUE and Curly=42. Joe and Moe are TRUE if they are any non-zero value, which is not the intent. The correct form of that statement is: If Joe=42 And Moe=42 And Curly=42 Then Msgbox "all are 42" Returning to your IF statement, if the variables x, y and z are Boolean types, the simplest way to test if all 3 are TRUE is: If x And y And z Then Msgbox "all are true" There is no need to write "x = true". In a conditional expression, simply "x" is equivalent. And tests like "not z = false" give me a migraine :-). "Not z = false" is the same as "z = true", which is the same as simply "z" in a conditional expression. By the way, "not z = false" is also the same as "z < false", although that still gives me slight headache :-). Forgive me if I am stating what is now "obvious" to you. I notice that you switched to tests like "x = false or y = false or ..." in your last attempt. But as demonstrated below, that can be written simply as "not x or not y or ....". For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False There are 8 combinations to test, not 4. The following is how I would test all combinations. You should copy-and-paste this example if you want to try it. Dim x As Boolean, y As Boolean, z As Boolean, i As Integer Debug.Print "-----" For i = 0 To 7 x = (i Mod 2 = 1) y = (i \ 2 Mod 2 = 1) z = (i \ 4 Mod 2 = 1) Debug.Print i, x, y, z, If x And y And z Then Debug.Print "all are true" ElseIf Not x Or Not y Or Not z Then Debug.Print "at least one is false" Else Debug.Print "logic error!" End If Next i You can substitute Msgbox for Debug.Print. But I think the Immediate Window is a much better approach. Press ctrl-G to see the Immediate Window. ----- original message ----- "Dan Thompson" wrote in message ... Thanks for the advice I fixed the problem and I realize it was to do with the order of logic in my code. I understand now that however you present your condition in an "IF" statement the "IF" statement always hast to evaluate to be true in order for VBA to execute the code in the "IF" statement. Bob = 42 IF Bob = 42 Then MsgBox "Happy Birthday" (Condition executes because it is TRUE that Bob is 42) Bob =35 IF Not Bob = 42 Then Exit Sub (Condition executes because it is TRUE that Bob is NOT 42) Both If statements execute because they are evaluated to be true Right ?? on the other hand Bob = 42 IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is FALSE and the "IF" statement will only execute code when the "IF" statement returns true) I think I got it :) btw I looked at that link you sent me and it just made my head spin lol too deep for me Dan Thompson "JLGWhiz" wrote: I get all screwed around with the logic tables and have to use trial and error when I get into testing multiple boolean results. Here is a site that explains where the problem comes from. http://www.rwc.uc.edu/koehler/comath/21.html The If statement has to capture the condition so that the result is true to make the command line execute, even if the result is negative. This means that you can check to see if it Is, or you can check to see if it is Not, but you cannot check to see if it is maybe. So when you use the And or the Or operators in the criteria line of the If statement you need to be aware of what the logic is doing in evaluating the statement. "Dan Thompson" wrote in message ... Hey JLGWhiz This is what I am trying to accomplish only I would like to do it in the shortest amount of code as possible. I think my code is a little long for this macro. here is the code Sub test() Dim one As Boolean, two As Boolean, three As Boolean Dim Count As Integer one = True two = True three = True For Count = 1 To 4 If Count = 1 Then one = False If Count = 2 Then two = False If Count = 3 Then three = False MsgBox "Count # " & Count If one = False Or two = False Or three = False Then MsgBox "One or more of the 3 conditions are false" End If If one And two And three = True Then MsgBox "All of the 3 conditions are true" End If one = True two = True three = True Next Count End Sub "JLGWhiz" wrote: Also: If Not one = True Or Not two = True Or Not three = False Then MsgBox "One or more of the 3 conditions are false" End If "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional "If Not ... Then" Statement not working properly Help!
Sub test()
Dim one As Boolean, two As Boolean, three As Boolean one = True two = False three = True If Not one * two * three Then MsgBox "not all true" Else MsgBox "all true" End If End Sub "Dan Thompson" wrote in message ... The following code should pop up a message box but it is not doing so where am I going wrong ? Sub test() Dim one As Boolean, two As Boolean, three As Boolean one = True two = True three = False If Not one = True And Not two = True And Not three = False Then MsgBox "One or more of the 3 conditions are false" End If End Sub Dan Thompson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Adjust row height" not working properly | Excel Discussion (Misc queries) | |||
Array function not working correctly if I use an "or - statement" | Excel Programming | |||
Unexpected Result with "If" Logic/Conditional Statement | Excel Discussion (Misc queries) | |||
Unexpected Result with "If" Logic/Conditional Statement | Excel Discussion (Misc queries) | |||
Can I include a "validation drop down" in a conditional statement? | Excel Worksheet Functions |