jeudi 17 novembre 2016

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

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!

Aucun commentaire:

Enregistrer un commentaire