Type mismatch error with my nested if, is empty code

[Moonbeam111]

I have two questions. I hope its not against the message board rules to ask 2 questions in 1 thread but I figure why not.

The first is: I am not sure why I'm getting a type mismatch error here. I know I could make this code prettier somehow by using a "for" statment to check each cell but I'm just used to the "if" statements.

If Range("D141") = "example message" And Range("D151") = "example message" Or IsEmpty(Range("C143")) And Range("D161") = "example message" Or IsEmpty(Range("D153")) And Range("D171") = "example message" Or IsEmpty(Range("C163")) And Range("D181") = "example message" Or IsEmpty(Range("C173")) And Range("D191") = "example message" Or IsEmpty(Range("C183) And Sheets("Sheet1").Range("G25") > Sheets("Sheet2").Range("E25") Then

'do something

The second is: How do check if a macro button has been called? For example, I call button4_click and in another macro I would like to know if button4_click has been called and if so 'then do something else. I've searched around but couldn't find a clear answer for myself.

 

[Joe4]

I am not sure why I'm getting a type mismatch error here.

You are missing a double-quote after your range on this part here:

IsEmpty(Range("C183)

Also, you have a mixture of OR and AND statements. Without using parentheses to tell Excel how to group those so it checks it the way you want, I doubt your code will do what you intend.

How do check if a macro button has been called?

One way is to add a flag on the sheet somewhere (or possibly a global variable in VBA) that you can use to indicate that it has been run.

 

[Moonbeam111]

Also, you have a mixture of OR and AND statements. Without using parentheses to tell Excel how to group those so it checks it the way you want, I doubt your code will do what you intend.

How would I go about grouping the parenthesis? Basically I want each and to match with each or in pairs except this: If Range("D141") = "example message" which should always be true. I get a "list seperator" error when I try putting in more parenthesis. Should I take a different approach and use a different type of statement besides "if cell" (such as for each cell) since its not working? And if so, could I get an example?

One way is to add a flag on the sheet somewhere (or possibly a global variable in VBA) that you can use to indicate that it has been run.

I currently use a combination of a boolean, an integer and a global variable and it works pretty well. But can you give me an example of how you would do it? Like your actual code.

 

[Joe4]

The parentheses go back to the days of school math and logic. So, if our conditions/criteria are represented by letters, then this:

If (A and B) or (C and D) Then

would be interpretted like this:
Your first evaluate everything within parentheses first, and then what is outside last.
So in order for that statement to be true:
EITHER
- BOTH A and B must be true
OR
- BOTH C and D must be true

If either of those blue lines is true, your whole statement will evaluate to true.

Now, if you had something like this:

If (A or B) and (C or D) Then

that means that BOTH of the following lines MUST be true in order to return true:
- EITHER A or B must be true
AND
- EITHER C or D must be true

That is the only way that statement can be true.

And these patterns follow regardless of how many conditions you may have.
Whenever mixing AND and OR statements in the same IF, you should always use parentheses to group them appropriately so the check works the way you want it to.

Regarding the Macro button question, I think we need to clarify one thing first. How long of a memory does it need to have?
I mean, do you want to know if the button4_click Macro was called:
- today only
- ever
- just in this one cycle (if you have VBA code calling other code)

 

[Moonbeam111]

Thanks for the clarification Joe. I will double check my parenthesis. As far as the macro question, I meant just in this is once cycle. Or at most 2 or 3 macro button calls later.

 

[Joe4]

Thanks for the clarification Joe. I will double check my parenthesis. As far as the macro question, I meant just in this is once cycle. Or at most 2 or 3 macro button calls later.

I would probably just have code at the beginning of your button4_click Macro that populate some unused cell, like AZ1 with the current date, i.e.

Range("AZ1").Value = Date

Then, you can add code to your other procedures and see if the value in that cell matches today's date, i.e.

If Range("AZ1").Value = Date Then ...

There are other ways of doing this, this is just an easy one that I might use in this scenario.

 

[Moonbeam111]

For those wondering. I figured out what was wrong. It turns out that this cell that was referenced

Sheets("Sheet2").Range("E25")

had an error #N/A in it from a formula I put in it earlier. When I fixed that and then changed my if statement to look like this:

If Range("D141") = "example message" Then
If Range("D151") = "example message" Or IsEmpty(Range("C143")) Then
If Range("D161") = "example message" Or IsEmpty(Range("D153")) Then
If Range("D171") = "example message" Or IsEmpty(Range("C163")) Then
If Range("D181") = "example message" Or IsEmpty(Range("C173")) Then
If Range("D191") = "example message" Or IsEmpty(Range("C183")) Then
If Sheets("Sheet1").Range("G25").Value > Sheets("Sheet2").Range("E25").Value Then
' code here
End If
End If
End If
End If
End If
End If

It worked as intended.