CODEWITHSUNDEEP
vbaexcel
paulinhax
paulinhax

Reputation: 602

excel: testing conditions with if

I'm trying to check different conditions using VBA for filling my columns and rows.

This is what I've tried so far:

For i = 1 To Lastrow
    For j = 1 To 11
        If (IsEmpty(ws1.Range("C12"))) And (IsEmpty(ws1.Range("D12"))) = True Then
            ws2.Cells(i, j) = ws1.Cells(i, j).Value
        ElseIf (IsEmpty(ws1.Range("C12")) And (ws1.Cells(i, 8) <= ws1.Cells(12, 4))) = True Then
            ws2.Cells(i, j) = ws1.Cells(i, j).Value
        ElseIf (IsEmpty(ws1.Range("D12")) And (ws1.Cells(i, 8) <= ws1.Cells(12, 4))) = True Then
            ws2.Cells(i, j) = ws1.Cells(i, j).Value
        ElseIf (ws1.Cells(12, 3) >= ws1.Cells(i, 7) And ws1.Cells(12, 3) <= ws1.Cells(i, 8)) Or (ws1.Cells(12, 4) >= ws1.Cells(i, 7) And ws1.Cells(12, 4) <= ws1.Cells(i, 8)) Or ((ws1.Cells(12, 3) >= ws1.Cells(i, 7)) And (ws1.Cells(12, 4) <= ws1.Cells(i, 8))) Then
            ws2.Cells(i, j) = ws1.Cells(i, j).Value
        End If
    Next j
Next i

As you can see, I test a lot of conditions.

The problem is with the statements where cells C12 OR D12 are empty. If D12 is empty the code should give me all dates that stated from date C12.

For example, I want everything that starts from 15/05/2017:

enter image description here

And that's the result I got:

enter image description here

As you can see, it's not correct. I tested everything separately and it works, but when I get everything togheter it gets a few mistakes.

In addition, I'm really in doubt if my If/ElseIf statements are testing all conditions in order or if I wrote in a proper way. Is there another way to run it in the order I want?

Upvotes: 0

Views: 57

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Haven't validated your IFs but a Select statement would be much cleaner:

For i = 1 To lastrow
    For j = 1 To 11
        Select Case True
            Case IsEmpty(ws1.Range("C12")) And IsEmpty(ws1.Range("D12")):
                ws2.Cells(i, j) = ws1.Cells(i, j).Value

            Case IsEmpty(ws1.Range("C12")) And ws1.Cells(i, 8) <= ws1.Cells(12, 4):
                ws2.Cells(i, j) = ws1.Cells(i, j).Value

            Case IsEmpty(ws1.Range("D12")) And ws1.Cells(i, 8) <= ws1.Cells(12, 4):
                ws2.Cells(i, j) = ws1.Cells(i, j).Value

            Case (ws1.Cells(12, 3) >= ws1.Cells(i, 7) And ws1.Cells(12, 3) <= ws1.Cells(i, 8)) Or _
                 (ws1.Cells(12, 4) >= ws1.Cells(i, 7) And ws1.Cells(12, 4) <= ws1.Cells(i, 8)) Or _
                 (ws1.Cells(12, 3) >= ws1.Cells(i, 7) And (ws1.Cells(12, 4) <= ws1.Cells(i, 8))):
                ws2.Cells(i, j) = ws1.Cells(i, j).Value

            Case Else:
                ws2.Cells(i, j) = "No match"
        End Select
    Next j
Next i

Upvotes: 2

Related Questions