"Or" statement not working

[Guest]

This Macro excerpt is based on a "Check Cell Content before continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements (Conditions
3, & 2 are met first) then the Macro continues even though the 1st condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

 

[Don Guillett]

Do you want AND instead of OR?

 

[Guest]

If any of these conditions exist, then I want the message. Only if all are
met do I want to continue the macro. I believe that means I need an "or"
function.

 

[Don Guillett]

I just tested this and it worked fine. Perhaps you don't want = but do want
<> (NOT equal)

Sub checkit()
If Range("h8").Value = "F" Or _
Range("h6").Value = Range("h7").Value Or _
Range("h4").Value = Range("H2").Value _
Then
MsgBox "dont do it"
Exit Sub
Else
MsgBox "doit"
'Do macro.
End If

End Sub

 

[Guest]

Thank You, that does appear to work.

Another solution I came up with was to simply add an "IF(OR..." function, in
a variables area, that provides a Yes/No output. I then changed the "If"
statement in my code to look only at that cell. (If Range("BZ28").Value =
"NO" _)

Now I need to decide which will make more sense to me when I try to edit
this in 6 months.

Thanks again,
Christopher McCune

 

[Don Guillett]

I would stay within the macro but if you insist on doing that why not one
cell
if(a=a,1,0)+if(b=b,1,0)
then
if range("a2")<3 then msgbox "bad boy"

 

[Bill Renaud]

Hi Chris,

Sounds like you are still having basic logic problems. In this situation, I
normally revert back to a "truth table" concept to sort out all of the
combinations before attempting to write the program. It would be something
like this (using 'short-form' notation):

Situation Action
---------------------- --------
Q44.Value="Fill in..." MsgBox
F47 is empty MsgBox
H56 is empty MsgBox
F47.Value=AB2.Value Do macro
H56.Value=AH2.Value Do macro

Remember that empty cells are considered to be 0, so if F47 is empty
(hasn't been filled in by the user yet) and AB2 =0, then they will be
considered equal by Excel. Complete the table as I have suggested above and
post it here, then we'll be able to fix the logic.

I'm suspecting that you probably need a separate If statement to check the
value of Q44 first and display the MsgBox, regardless of the state of the
other 2 cells. The situation with the other 2 cells (F47 and H56) is
probably more complex. You might need a separate Boolean variable to keep
track of everything, then finally show the MsgBox and exit if the Boolean
variable (blnShowMsgBox?) is TRUE.

 

[Bill Renaud]

Never mind the post immediately above; it is incomplete. I should have
expanded it out to something like the following (hyphen means "don't
care"):

Q44 F47 H56 Action
-------------- ----- ----- ------
="Fill in..." - - MsgBox
- empty - MsgBox
- - empty MsgBox
- =AB2 - MsgBox
- - =AH2 MsgBox
<>"Fill in..." <>AB2 <>AH2 Do macro

Chris T-M wrote (in another post):
<<If any of these conditions exist, then I want the message. Only if all
are met do I want to continue the macro.>>

This appears to be contradictory. If any conditions exist (OR), then show
the message. If all are met (AND) then do the macro. This does not make
sense to me.

As an alternative, I sometimes use a program structure like the following
(or declare ShowMsgBox to be an error handler and use Err.Raise):

'----------------------------------------------------------------------
Sub CheckCellContent()
Dim strMsgBox As String

If Range("Q44").Value = "Fill in..." Then GoTo ShowMsgBox
If IsEmpty(Range("F47")) Then GoTo ShowMsgBox
If IsEmpty(Range("H56")) Then GoTo ShowMsgBox

If OtherConditions _
Then
'Do macro.
End If

GoTo ExitSub

ShowMsgBox:
strMsgBox = "You have not filled in all" & vbNewLine & _
"fields required to generate" & vbNewLine & _
"a complete project number." & vbNewLine & _
vbNewLine & _
"Please fill in the Abbreviation," & vbNewLine & _
"Project Type, Class, and Application" & vbNewLine & _
"Date fields (blue font with an *)."

MsgBox strMsgBox, _
vbCritical + vbOKOnly, _
"Incomplete Project Number"
ExitSub:
End Sub

 

[Guest]

The Function I placed in "BZ28" was...

=IF(OR(Q44="Fill in…",Q44=0,F47=AB2,H56=AH2,L47=0),"NO","YES")

....and works without error, but I am still at a loss as to why logic that
works as a function, doesn't work in a Macro (programming language asside),
but as I am new to VBA I suppose I'll just have to reset my thought process
for VBA.

Thanks again, Chris T-M

 

[Guest]

OK you lost me. Did I mention I am new to VBA?
That being said, the logic I used in the Macro works if used as a Function,
so why wouldn't it work in a Macro (programming language asside)?

Regardless of my understanding, setting this up as an "On Error GoTo..."
does work.

As for my previous contridiction, I appologize. What I meant to say was:
If any are true, then show message.
If all are false, then do macro.

For the Macro Logic (broken out):

If Range("Q44").Value = "Fill in..." Then GoTo ShowMsgBox
If Range("F47").Value = Range("AB2").Value Then GoTo ShowMsgBox
If Range("H56").Value = Range("AH2").Value Then GoTo ShowMsgBox

The Value = Value cells are text values. Both effectively contain
instructions for what data is desired unless one of them is filled in. If one
is filled in, then I can concatelate a statement such as "The name of the new
project is" "NewProj".

Thank you for the additional advice,
Chris T-M

 

[Don Guillett]

Your original did what you asked it to. You were just asking the wrong thing
so you got a wrong answer.

 

[Bill Renaud]

<<OK you lost me.>>
Where did we lose you? (We are the ones that are lost!!! )

<<...the logic I used in the Macro works if used as a Function, so why
wouldn't it work in a Macro?>>
I think you have this backwards. In general, everything will work in a
macro, but will not necessarily work in a function. For example, you can
set other cells (run your macro after deciding that everything is OK),
insert new worksheets into the workbook, run other commands, but you can't
do those things if the function were called from a formula in a worksheet
cell.

<<What I meant to say was:
If any are true, then show message.
If all are false, then do macro.>>

Then the logic in your original post should work, as that is exactly what
it does. It will check all 3 conditions, and if ANY of the 3 are TRUE, then
it will display the MsgBox and exit. So, if Q44 has some other value in it
(i.e. "Project X"), but F47 is equal to AB2, then your macro will run (the
Else part of the If statement).

If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox ...
Exit Sub
Else
'Do macro.
End If

 

[Guest]

I think rephrasing may resolve several issues:

A) the initial issue...
Macro Logic:
....If argument 1 is True, or argument 2 is true, or argument 3 is true, then
message and end. Else continue...

so... check all 3 conditions, and if ANY of the 3 are TRUE, then display the
MsgBox and exit...

What was occuring:

If 1 & 2 were false, but 3 was still true then message...

This general pattern was true for all cases except the following:

If 3 & 2 were false, but 1 was still true, then instead of the message, the macro continued. This occured with all n+2 variables. If condition 1 was the last condition that was true, then the macro would continue, even though the logic said not to.

B) Using a worksheet cell to calculate the function works, so I am content,
just confused as to what was occuring. I am OK chalking this up to "who
knows" or my inexperience with VBA.

I appreciate everyones input. I actually learned a lot from the exchange.
Thank you & Don for the support.
Chris T-M

 

[Bill Renaud]

<<If 3 & 2 were false, but 1 was still true, then instead of the message,
the macro continued. This occurred with all n+2 variables. If condition 1
was the last condition that was true, then the macro would continue, even
though the logic said not to.>>

This tells me that condition 1 was actually false. In this situation, it is
time to open up the Immediate window and test this theory.
In the Immediate window, type a question mark ("?") followed by the
condition, so it will look like the following:

?Range("Q44").Value = "Fill in..."

.... then hit the Enter key and see what value appears on the next line
underneath. It should be either TRUE or FALSE.

 

[Guest]

You're right it shows as false. I did a copy & paste to write the macro, so I
am at a loss as to what the issue is. The cell is formatted as text...

Thanks,
Chris T-M

 

[Bill Renaud]

OK, what is the formula (or complete value) in Range("Q44")?

 

[Guest]

It is the text "Fill in..." The first letter capitalized, the rest lower
case, and 3 periods, copied from the cell into the Macro using Ctrl+C, &
Ctrl+V. There is also conditional formatting to set the color if the cell no
longer says "Fill in..." or is blank, and a Validation - Input Message.

(PS: Replying to me at 10:30 on a Friday night seems above & beyond the call
of duty, but I do appreciate it.)

 

[Don Guillett]

You may have a TRIM issue?

 

[Guest]

New to VBA. Please explain what a TRIM issue is. MS VB Help didn't provide an
explanation I understood how to apply.

FYI: Looking at the text again for a space, I noticed that my cursur jumped
all 3 periods together. I've seen MS Word "fix" typing like this, but could
this be why a macro isn't seeing "..." (presumably one character), and "..."
(three characters) as the same?
Chrs T-M

 

[Don Guillett]

Send me a file to the address below along with snippets of these messages
and exactly what you want.