How to make an IF Statement with OR and an empty cell in Excel VBA

Question

I am trying to make an IF Statement in a VBA script. What it ultimately wants to achieve is to copy content from one sheet ("Database") to another sheet ("Search"), given the fulfilment of an IF statement.

The script starts by defining variables ("country", "category") as dependent on user input on cells "E5" and "E7" of the "Search" sheet. It also defines the final row for the If statement to run until that row:

country = Sheets("Search").Range("E5").Value
category = Sheets("Search").Range("E7").Value
finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

After this, the script establishes the condition: If the inputted values (in the cells "country" and "category") are matched by content on cells of the "Database" sheet, then the values on this sheet should be copied to the "Search" sheet:

For i = 2 To finalrow
    If Sheets("Database").Cells(i, 1) = country And _
    Sheets("Database").Cells(i, 3) = category Then
      With Sheets("Database")
        .Range(.Cells(i, 1), .Cells(i, 9)).Copy
      End With
      Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats            
    End If                   
Next I

I would like to add an additional condition to the IF statement with an OR statement. I would like to make it so that if the user does not fill the "category" cell, the values are still copied from one sheet to another. In terms of code, I added the part between ** but it is not working:

For i = 2 To finalrow
    If Sheets("Database").Cells(i, 1) = country And _
    Sheets("Database").Cells(i, 3) = category **Or category = ""** Then
      With Sheets("Database")
        .Range(.Cells(i, 1), .Cells(i, 9)).Copy
      End With
      Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats           
    End If                   
Next i

Apart from this, everything is working out fine. Do you have any idea of what I might be doing wrong? Thank you!

Why do you check if category is equal to a single blank character? Did you mean Or category = "" (meaning, category is equal to an empty string)? — FDavidov
Additionally, AND and OR have different priorities, so it is best if you use parenthesis to force a specific order in which the checks are taking place. — FDavidov
Please revise with your parentheses. There's a possible difference between If A And (B Or C) and If (A And B) Or C — David Zemens

Glitch_Doctor Glitch_Doctor · Accepted Answer · 2016-11-17T15:06:26

I'd just avoid the Or entirely if it's giving you issues. also putting a space in " " is literally looking for space as a value, if you mean for it to be blank use "" or IsEmpty(Category):

For i = 2 To finalrow
    If Category = "" Then

        If Sheets("Database").Cells(i, 1) = country Then

            With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
             Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        End If

    Else

        If Sheets("Database").Cells(i, 1) = country And _
        Sheets("Database").Cells(i, 3) = Category Then

            With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
             Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        End If

Next i

How to make an IF Statement with OR and an empty cell in Excel VBA

1 Answers