2

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.

Improve this question
3
  • 1
    Where is the code where you initiate country? Is seems that this is merely a Sting containing one country. If that's correct then you are pretty much allowing for no more than one country in your code: ElseIf Sheets("Database").Cells(i, 1) = country And _. So, Cells(i, 1) must contain exactly what is in country otherwise the If statement will not work (especially since it is logically combined with an AND). Hence, you will (should) always get the no such country error message. Commented Nov 18, 2016 at 11:32
  • I didn't include it in the question because I thought it would just be confusing. Here it is: country = Sheets("Results").Range("D5").Value category = Sheets("Results").Range("D6").Value subcategory = Sheets("Results").Range("D7").Value finalrow = Sheets("Database").Range("A200000").End(xlUp).Row Commented Nov 18, 2016 at 11:35
  • 1
    Just as I thought. So, basically, country gets the value of Sheets("Results").Range("D5").Value If this cell contains USA then your line ElseIf Sheets("Database").Cells(i, 1) = country And _ compares Sheets("Database").Cells(i, 1) to USA. If they are equal then your ElseIf can work (if the other if cases are met). Yet, only USA will be accepted. The following will not work: _USA, uSA, USA , usa, etc. Also, only USA is accepted (no other country). Commented Nov 18, 2016 at 11:55

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

and just to add to arcadeprecinct's post above, when doing the .Find I'd turn off the debugger immediately beforehand and resume it immediately after doing the .Find - To turn it off enter On Error Resume Next and to resume it (which is very important that you do!) it's On Error GoTo 0 - This will avoid the macro crashing
@Jeremy Imo the better option would be to set a range using .Find and check if it's Nothing. Since it's only executed once the possible performance boost is not worth the downsides.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.