vba Excel VBA中的If Then ElseIF语句

If Then ElseIF Statement in Excel VBA

提问by franciscofcosta

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.

我正在编写一个运行数据库搜索的脚本。它使用带有多个条件的 IF 语句来实现，这些条件取决于用户在三个不同字段（“国家/地区”、“类别”和“子类别”）中的输入。我之前已经定义了这些。

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:

我现在想在 IF 语句中为另外两种情况添加更多条件：

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

1 - 用户提供了数据库中不存在的国家。我写了如下：

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:

2：用户提供了数据库中不存在的国家和数据库的组合。我写了如下：

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 ElseIfstatements, the script is running fine and the results from the database search are presented. However, when I add these two statements, only the MsgBoxfrom the third Ifstatement (reading "There is no such country in the database. Please search for information relating to another country.") shows.

正如我所说，没有这两个附加ElseIf语句，脚本运行良好，并显示数据库搜索的结果。但是，当我添加这两个语句时，只有MsgBox第三个If语句（阅读“数据库中没有这样的国家。请搜索与另一个国家相关的信息。”）显示。

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.

当我添加另外两个语句时，您知道我可能做错了什么吗？感谢您的帮助。

采纳答案by arcadeprecinct

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

当您只有两个第一个If/ElseIf块时，循环将运行直到到达第二个条件计算True并执行第二个块的行。其他时间什么都不执行。

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 .Findfirst 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)

.Find如果找不到匹配项，我将首先使用并退出搜索国家，然后进行搜索。理想的方式将取决于您的数据（是否有很多预期的匹配等）

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.

相同的逻辑适用于最后一个块，尽管这只会在找到国家/地区但类别不同时才会导致问题。进行搜索并检查之后是否有任何匹配项可能是最简单的。如果没有，你可以给消息。