If Then ElseIF Statement in Excel VBA

Question

I am writing a script that runs a Database search. It does so with an IF statement that bears several conditions, which are dependent on user input in three different fields ("country", "category" and "subcategory"). I have defined these previously.

Different combinations of the input by the user in the three fields will produce different outcomes. As such, for instance, if the user does not provide a "country", then the search will not run and an error message will pop up, as follows:

For i = 2 To finalrow

    If country = "" Then
        Sheets("Results").Range("B10:J200000").Clear
        MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided."
        Sheets("Results").Range("D5").ClearContents
        Sheets("Results").Range("D6").ClearContents
        Sheets("Results").Range("D7").ClearContents
        Exit Sub

If the user provides a country, then the search runs, showing more or less results depending on the user having or having not provided also a category and subcategory:

ElseIf Sheets("Database").Cells(i, 1) = country And _
        (Sheets("Database").Cells(i, 3) = category Or category = "") And _
        (Sheets("Database").Cells(i, 4) = subcategory Or subcategory = "") Then

            With Sheets("Database")
            .Range("A1:I1").Copy
            End With
            Sheets("Results").Range("B10:J10").PasteSpecial

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

    End If

Next I

This is working all fine.

I now want to add more conditions to the IF statement for two additional cases:

1 - The user provides a country that does not exist in the database. I have written this as follows:

ElseIf Sheets("Database").Cells(i, 1) <> country Then
        MsgBox "There is no such country in the database. Please search for information relating to another country."
        Sheets("Results").Range("D5").ClearContents
        Sheets("Results").Range("D6").ClearContents
        Sheets("Results").Range("D7").ClearContents
        Exit Sub

2: The user provides a combination of a country and database that does not exist in the database. I have written this as follows:

ElseIf (Sheets("Database").Cells(i, 1) = country) And _
        (Sheets("Database").Cells(i, 3) <> category) Then
        MsgBox "There are no records in the database that match your search criteria. Please try another search"
        Sheets("Results").Range("D5").ClearContents
        Sheets("Results").Range("D6").ClearContents
        Sheets("Results").Range("D7").ClearContents
        Exit Sub

As I said, without these two additional ElseIf statements, the script is running fine and the results from the database search are presented. However, when I add these two statements, only the MsgBox from the third If statement (reading "There is no such country in the database. Please search for information relating to another country.") shows.

The entire code, with the two additional statements, is as follows:

For i = 2 To finalrow

If country = "" Then
    Sheets("Results").Range("B10:J200000").Clear
    MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided."
    Sheets("Results").Range("D5").ClearContents
    Sheets("Results").Range("D6").ClearContents
    Sheets("Results").Range("D7").ClearContents
    Exit Sub

ElseIf Sheets("Database").Cells(i, 1) = country And _
    (Sheets("Database").Cells(i, 3) = category Or category = "") And _
    (Sheets("Database").Cells(i, 4) = subcategory Or subcategory = "") Then

        With Sheets("Database")
        .Range("A1:I1").Copy
        End With
        Sheets("Results").Range("B10:J10").PasteSpecial

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

    Me.Hide

ElseIf Sheets("Database").Cells(i, 1) <> country Then
    MsgBox "There is no such country in the database. Please search for information relating to another country."
    Sheets("Results").Range("D5").ClearContents
    Sheets("Results").Range("D6").ClearContents
    Sheets("Results").Range("D7").ClearContents
    Exit Sub

ElseIf (Sheets("Database").Cells(i, 1) = country) And _
    (Sheets("Database").Cells(i, 3) <> category) Then
    MsgBox "There are no records in the database that match your search criteria. Please try another search"
    Sheets("Results").Range("D5").ClearContents
    Sheets("Results").Range("D6").ClearContents
    Sheets("Results").Range("D7").ClearContents
    Exit Sub

End If

Next I

Do you have any idea of what I might be doing wrong when I add the two additional statements? Thank you for the help.

Answer 1

When you only have the two first If/ElseIf blocks, the loop runs until it gets to a line where the second condition evaluates to True and executed the second block. All the other times nothing is executed.

When you include the third block, the country will (most likely) not match in the first row so it will give you the message that the country is not in the database (although it actually only is not in the current row).

I would search the country using .Find first and exiting if no match was found and then do the search. The ideal way would depend on your data (are there many expected matches etc)

The same logic applies to the last block although this will only cause a problem once the country is found but with a different category. It is probably the easiest to just do the search and check if there were any matches afterwards. If not, you can give the message.