If And Or Multiple Statements in VBA

Question

I want redistribute an Excel file with 14 Columns to the correct Column (with 12.000 rows).

For this I have to use some If And Or Statements to put the numbers in a matrix. But apparently I don't get the right things out of it.

It makes all my cells zero, while the cells with a value should keep the value.

Where do I go wrong?:

    For i = 1 To LastRow
    If Cells(i, 8).Value2 = "" Then Cells(i, 8).Value2 = 0

    If Cells(i, 1).Value2 < 437 And Cells(i, 5).Value2 = "aa" _
    Or Cells(i, 5).Value2 = "bb" _
    Or Cells(i, 5).Value2 = "cc" _
    Or Cells(i, 5).Value2 = "dd" _
    Or Cells(i, 5).Value2 = "ee" _
    Or Cells(i, 5).Value2 = "ff" _
    Or Cells(i, 5).Value2 = "gg" _
    And Cells(i, 7).Value2 = "" _
    Then Cells(i, 7).Value2 = 0

    Next i

So if the cell contains an aa or bb or cc or dd or ee or ff or gg and is empthy the cell should become 0 otherwise it should stay the same value. After that it should go into a matrix

Then Matrixgetallen(i, 2) = CDbl(Cells(i, 7).Value2)

But I didn't manage to get that in the same if statement.

If have 6 of these kind of If-statements, so probably If Then Else doesn't work.

There is operator precedence also with boolean operators. AND has eminence opposite OR. Use parentheses to mark which boolean operations belongs together. — Axel Richter, Mar 13 '15 at 13:54
Can you give me an (short) example to clearify what you mean? — Helfenstein, Mar 13 '15 at 13:55
As it stands now, your If is true if `(Cells(i, 1).Value2 < 437 And Cells(i, 5).Value2 = "aa") Or...Or...Or...(Cells(i, 5).Value2 = "gg" And Cells(i, 7).Value2 = "")` because And comes first before Or. — Axel Richter, Mar 13 '15 at 14:02

score 6 · Accepted Answer · edited May 23 '17 at 12:14

Hard to optimise without seeing your full code but for this portion:

Break the AND into two IFs as VBA doesn't short circuit
Rather than a long sequence of ORs, do a single shot test against an array (a numeric result means the exact string is found)

code

i = 1
Dim strIN
strIN = Array("aaf", "bb", "cc", "dd", "ee", "ff", "gg")

If Cells(i, 1).Value2 < 437 Then
    If Len(Cells(i, 5)) = 0 Then
        Cells(i, 7).Value2 = 0
    Else
        If IsNumeric(Application.Match(Cells(i, 5), strIN, 0)) Then Cells(i, 7).Value2 = 0
    End If
End If

score 0 · Answer 2 · answered Mar 13 '15 at 14:03

0

Thank you for the hint Axel. I never have seen this before, but it works great! This did the trick:

    If Cells(i, 1).Value2 < 437 And (Cells(i, 5).Value2 = "aa" _
Or Cells(i, 5).Value2 = "bb" _
Or Cells(i, 5).Value2 = "cc" _
Or Cells(i, 5).Value2 = "dd" _
Or Cells(i, 5).Value2 = "ee" _
Or Cells(i, 5).Value2 = "ff" _
Or Cells(i, 5).Value2 = "gg") _
And Cells(i, 7).Value2 = "" _
Then Cells(i, 7).Value2 = 0

But I also really like the answers of Brett . That is a nice new way to approach it.

answered Mar 13 '15 at 14:03

Helfenstein

315
1
4
13

2

Just a tip for readability: `IF a AND b AND (c OR d OR e OR f) THEN g`. By putting the non-parenthetical clauses first, it makes it a bit easier to read. Also, you can use some indention of the continuation lines to help your eye pick it out, as well. When you (or someone else) comes across this code in 6 months, that will make interpretation of the code easier for the eye, without making any difference in the actual execution. (Yes, I understand you went with the other answer, just some friendly advice.) – FreeMan Mar 13 '15 at 16:05

If And Or Multiple Statements in VBA

2 Answers2

Linked