View Full Version : Ifs, ors, and nots
davidman
12-28-2014, 10:11 AM
I have a macro to add new employees to a file, I need to filter it by shift and manager as we don’t look at all teams or shifts. The macro is doing nothing, and I think that the line that is causing trouble is
If Shift <> "" Or Shift <> "Shift1" Or Shift <> "Shift2" Or Shift <> "Shift3" Or Shift <> "Shift4" Then
Is the line here correct? Or should it be
If Not (Shift = "" Or Shift = "Shift1" Or Shift = "Shift2" Or Shift = "Shift3" Or Shift = "Shift4") Then
I want it to filter out those shifts, so the macro will do things to anybody not on those shifts.
Paul_Hossler
12-28-2014, 11:22 AM
If Not (Shift = "" Or Shift = "Shift1" Or Shift = "Shift2" Or Shift = "Shift3" Or Shift = "Shift4") Then
Trying to turn your description into VBA, I came up with ...
If ((Shift <> "") And (Shift <> "Shift1") And (Shift <> "Shift2") And (Shift <> "Shift3") And (Shift <> "Shift4")) Then
'Do Stuff
End If
Boolean logic rules would combine Not and And and Or, but I went for what I thought was the simplest with each piece of the truth table with its own (...)
Substituting
X = for
If True Then
X = A or B or C
If any of the above are true then X = True
X = Not(A or B or C)
If any of the above are True then X = False
If Not (Shift = "" Or Shift = "Shift1" Or Shift = "Shift2" Or Shift = "Shift3" Or Shift = "Shift4") then X = False
If it is any of the above shifts, then skip to the ELSE
X = Not(A) or Not(B) or Not (C)
If any of the above are False then X = True
If A is False then Not(A) is True
X = A and B and C
Unless all the above are True Then X is False.
X = Not(A and B and C)
If any of the above are false, then X =True
X = Not(A) and Not(B) and Not(C)
If any of the above are True than X is False
If Shift <> "" And Shift <> "Shift1" And Shift <> "Shift2" And Shift <> "Shift3" And Shift <> "Shift4" Then Do Something
If A is True then Not(A) is False.
If Shift <> "" Or Shift <> "Shift1" Or Shift <> "Shift2" Or Shift <> "Shift3" Or Shift <> "Shift4" Then
X = always True
Same as
If (X <> A) or (X <> B) then
X cannot be both therefore it is
Not(one of them) or Not(the other one)
Hopefully others will chime in with yet more ways of looking at the Boolean truth table, because it is very important to understand.
there are several more ways to make that decision. Depending on how many possible choices (total number of shifts) vs number of positive choices (how many shifts you deal with.
If you have fewer Shifts to use than the total number of shifts
If Shift = "Shift5" Or Shift = "Shift5" Then
'Do something
Else
'Do nothing
End If
Here is a bit more advanced way. It requires (at least) two subs
Sub Choicemaker()
Select Case Shift
Case "Shift5", "Shift6", "Shift7"
DoStuff1Sub
Case "Shift8"
DoStuff2Sub
Case Else
Exit Sub
End Select
End Sub
Sub DoStuff1Sub()
'Put your action code here
End Sub
Sub DoStuff2Sub
'Put alternate action code here
End Sub
We can offer much better advice if you explain what are you trying to do, and not how you think you want to do it?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.