CODEWITHSUNDEEP
excelvba
Helfenstein
Helfenstein

Reputation: 315

If And Or Multiple Statements in VBA

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.

Upvotes: 3

Views: 59676

Answers (2)

Helfenstein
Helfenstein

Reputation: 315

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.

Upvotes: 0

brettdj
brettdj

Reputation: 55692

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

  1. Break the AND into two IFs as VBA doesn't short circuit
  2. 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

Upvotes: 6

Related Questions