If Statement VBA

Question

I am writing a script in Excel VBA with an If and an ElseIf statements for a database search. The search is conducted through a UserForm that has two fields, labelled as Country and Category and defined in the script as follows:

Dim country As String
Dim Category As String
country = Sheets("Results").Range("D5").Value
Category = Sheets("Results").Range("D6").Value

The information is searched and presented in respect of the country searched and, as such, the minimum required for a search to run is the Country being provided by the user with a country that is in the database.

Taking the user-inputted criteria, the search runs through a table of data in a sheet called Database and pastes the results in another sheet called Results. Depending on the search criteria, the script will run several options prescribed by an If statement.

OPTION 1 - The user has provided a country and a category and:

• The country exists in the database but;
• The Category does not exist for the specific country.

In this case a MsgBox will pop up with saying that the specific combination of country and category provided by the user does not exist in the database. The message will ask the user if it would like to run a search just for all entries of the country provided, in this case. I have written the respective code as follows:

finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

For i = 2 To finalrow

    If Sheets("Database").Cells(i, 1) = country And _
        (Category <> "" Or Sheets("Database").Cells(i, 3) <> Category) Then
                Dim question As Integer
                question = MsgBox("Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you perhaps want to broaden your search and see all sources regarding " & country & "?", vbYesNo + vbQuestion, "Empty Sheet")
                   If question = vbYes Then
                        Sheets("Results").Range("D6").ClearContents
                        Category = Sheets("Results").Range("D6").Value
                        boolRestart = True
                    Else
                        Sheets("Results").Range("D5").ClearContents
                        Sheets("Results").Range("D6").ClearContents
                        Me.Hide
                        WelcomeForm.Show
                        Exit Sub
                    End If

OPTION 2 - The user has provided a country and:

• The country exists in the database and;
• The user has also provided a Category that exists in the database for the specific country or;
• The user has left the Category field empty.

In this case, the search will run. This is written in the script as follows:

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

        'Copy the headers of the "Database" sheet
        With Sheets("Database")
            .Range("A1:I1").Copy
        End With
        Sheets("Results").Range("B10:J10").PasteSpecial

        'Copy the rows of the table that match the search query
        With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
        Sheets("Results").Range("B20000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

    End If

I have tried to write the script in several different ways but the search engine keeps on not working as I want to. What is happening now is that when I input a Country that I know to be in the database, regardless of inputting a Category as well or not, OPTION 1 is always triggered. I have tried to take out OPTION 1 altogether and run just an If statement with OPTION 2 as is and the search runs fine with Country filled in and with both Country and Category filled in. However, as soon as OPTION 1 in in the code, this is always the option ran, regardless of what is inputted by the user.

The full code is here , for your reference:

Dim country As String 'Search query country, user-inputted
Dim Category As String 'Search query category user-inputted
Dim finalrow As Integer
Dim i As Integer 'row counter
Dim ws As Worksheet

Set ws = Sheets("Database")

country = Sheets("Results").Range("D5").Value
Category = Sheets("Results").Range("D6").Value
finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

For i = 2 To finalrow

    If Sheets("Database").Cells(i, 1) = country And _
        (Category <> "" Or Sheets("Database").Cells(i, 3) <> Category) Then
                Dim question As Integer
                question = MsgBox("Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you perhaps want to broaden your search and see all sources regarding " & country & "?", vbYesNo + vbQuestion, "Empty Sheet")
                   If question = vbYes Then
                        Sheets("Results").Range("D6").ClearContents
                        Category = Sheets("Results").Range("D6").Value
                        boolRestart = True
                    Else
                        Sheets("Results").Range("D5").ClearContents
                        Sheets("Results").Range("D6").ClearContents
                        Me.Hide
                        WelcomeForm.Show
                        Exit Sub
                    End If

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

        'Copy the headers of the "Database" sheet
        With Sheets("Database")
            .Range("A1:I1").Copy
        End With
        Sheets("Results").Range("B10:J10").PasteSpecial

        'Copy the rows of the table that match the search query
        With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
        Sheets("Results").Range("B20000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

    End If

Next I

Thank you very much for your help.

Answer 1

On (Category <> "" Or Sheets("Database").Cells(i, 3) <> Category) Then replace the OR for an AND

That check must consider both

1. The category is not empty AND
2. The category was not found

The way the IF is working is that it will always trigger if either

1. The category isnt empty (So, if you type anything in the category it will match here)
2. The category doesnt match (If the category is empty but it actually has anything on the list)

Answer 2

The problem is that your code will go to Option 1 if any line fails to meet the criteria, whereas we want it to only fail if every line fails to meet the criteria. We therefore need to do two scans of the data, the first to check if there are any passing lines (if not then we offer to clear the Category), and then another to copy the relevant data.

Try this:

Option Explicit

Private Sub CommandButton1_Click()

    Dim country As String 'Search query country, user-inputted
    Dim Category As String 'Search query category user-inputted
    Dim finalrow As Integer
    Dim i As Integer 'row counter
    Dim ws As Worksheet
    Dim foundMatch As Boolean
    foundMatch = False

    Set ws = Sheets("Database")

    country = Sheets("Results").Range("D5").Value
    Category = Sheets("Results").Range("D6").Value
    finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

    For i = 2 To finalrow
        If Sheets("Database").Cells(i, 1) = country And _
            (Sheets("Database").Cells(i, 3) = Category Or Category = "") Then
                foundMatch = True
        End If
    Next i

    If Not foundMatch Then
        Dim question As Integer
        question = MsgBox("Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you perhaps want to broaden your search and see all sources regarding " & country & "?", vbYesNo + vbQuestion, "Empty Sheet")
           If question = vbYes Then
                Sheets("Results").Range("D6").ClearContents
                Category = Sheets("Results").Range("D6").Value
            Else
                Sheets("Results").Range("D5").ClearContents
                Sheets("Results").Range("D6").ClearContents
                Me.Hide
                WelcomeForm.Show
                Exit Sub
            End If
    End If

    For i = 2 To finalrow
        If Sheets("Database").Cells(i, 1) = country And _
            (Sheets("Database").Cells(i, 3) = Category Or Category = "") Then
                'Copy the headers of the "Database" sheet
                With Sheets("Database")
                    .Range("A1:I1").Copy
                End With
                Sheets("Results").Range("B10:J10").PasteSpecial

                'Copy the rows of the table that match the search query
                With Sheets("Database")
                    .Range(.Cells(i, 1), .Cells(i, 9)).Copy
                End With
                Sheets("Results").Range("B20000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    Next i

End Sub

Answer 3

I think you should divide your code in three separated subroutines: - The first will run when a user triggers the search, then it must check if country has a valid value (giving error message if not) and then check if Category has a value, going to a second subroutine if it has value or going to a third subroutine if it is empty; - The second subroutine must get the value of both country and Category variables and return the expected result; - The third subroutine must get only country variable and return the expected result.

You can put both variables in the beginning of module (before any Sub and using Private instead of Dim) to leave them accessible to any subroutine in that module or you can create subroutines with parameters, where you can pass values to another Sub without making them accessible to all Subs in that module. I prefer the second alternative. In case you don't know how to pass parameters to another module, it is an example:

Sub QueryCountryAndCategory (QCountry as String, QCategory as String)

In this Sub, QCountry and QCategory are variables that will be accessible only in that module and it will receive values passed by caller subroutine, something like this (using your variables):

QueryCountryAndCategory(country, Category)

Or like this:

QueryCountryAndCategory(QCountry:=country, QCategory:=Category)

Remember that long codes are hard to maintain and hard to test. When your code becomes long, always consider dividing it in some Subs or Functions (which will return a value). It's also easier to test, as you can run each Sub alone to see if it is working properly.