Looping If Statement Problem

0

Hello,

I'm new to this forum and to VBA but am learning fast.  I have a IF statement working in a FOR-Next loop that wants to check if rows of data with dates in col.B is equal to previous month month number.

Example:

If Month(Date).Value - 1 = Month(Cells(K,2).Value)  And other conditions using OR before the THEN

.it copies entire row of matching month data from sheet1 to sheet2.  But vba is not gathering this comparison of month numbers.  I'm trying to keep this simple.  After the first line there is an AND for other conditional statements using OR and they by themselves work fine.  But VBA just skips over this first line ending with AND.  What am I missing or doing wrong. What happens is the macro copies any row with all different month dates using only the OR statements.

I hope I did this post correctly.

Thank you for your help

Answer
Discuss

Answers

0

By not publishing your code you have committed both of us to doing this the hard way. But since you say you are a fast learner, let's do it. Look at the basic IF statement.

If [Condition = True] Then
    ' do something
End if

The key to understanding is that the Condition is evaluated to return a boolean value, meaning, either True or False. Action is taken if the evaluation results in True.

Now you may argue that, in your case, there are several conditions. This is where you start going wrong. In an IF statement there can't be more than one condition. It's a matter of how you look at it or, better said, of how VBA looks at it. Take this worksheet function as an example:-

=OR(3 <> 4, 3 < 4, 3 > 1)

It combines 3 tests into a single expression which, in this case evaluates to True. In fact, =AND(3 <> 4, 3 < 4, 3 > 1) would also evaluate to True. So, let's translate this expression to VBA.

If 3 <> 4 Or 3 < 4 Or 3 > 1 Then

Clearly, the syntax does look like 3 conditions but VBA's If statement doesn't accept more than one. You can express that by setting parentheses.

If (3 <> 4 Or 3 < 4 Or 3 > 1) Then
' or even
If ((3 <> 4) Or (3 < 4) Or (3 > 1)) Then

When mixing And and Or conditions prentheses are essential. Remember that expressions between parentheses must be resolved before their results can be used in the larger calculation. Look for the difference in the following.

If 3 <> 4 Or 3 < 4 And 3 < 1 Then
and 
If 3 <> 4 And (3 < 4 Or 3 < 1) Then

In the second example the 2nd and 3rd expressions must be resolved together while they are separate in the first. In this particular example the result will be the same but that must not necessarily be so in every case.

So, to resolve your problem, set parentheses around your expressions and examine their logic. Put a break point next to the IF statement and examine each value of each expression in the Locals window when you run the code to the break point. VBA doesn't stop execution at the AND. It just evaluates your expressions differently from what you expect. Expect VBA to combine all 3 or 4 evaluations you have programmed into a single boolean value.

Discuss

Discussion

Hi DonC.

Hope the great answer from @Variatus sorts your problem. If you haven't already discovered it, Debug.Print can work inconjunction with the Locals window to understand how VBA is working. It can evaluate an expression (like your AND plus OR statement or whatever you have between IF and THEN) and show the result in the Intermediate window (made visible in VB Explorer by going View/ Intermediate Window).

That can be done from within the code or directly in the Intermediate Window. E.g in the Intermidiate window, paste this:
Debug.Print (4 <> 4 Or 3 < 4 And 0 < 1) & " c.f. " & (4 <> 4 And (3 < 4 Or 0 < 1))
and press return. Below it, you'll get the result "True c.f. False" showing how the brackets affected the decision.
John_Ru (rep: 1002) Apr 4, '21 at 10:59 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login