I am trying to write a script for a country-based Database search. The search is ran on a sheet full of data called "Database" and the results pasted to a different sheet called "Results".
The search is dependent on user-inputted variables, which I have defined as "Country", "Category" and "Subcategory":
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
I want the search criteria filled in by the user through a UserForm to lead to different scenarios. As such:
1 - If the user searches for a country that is not included in the database, the search will not run and a message saying so will pop up. I used a .Find function to so:
With Worksheets("Database")
Set c = .Range("A:A").Find(What:=country, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
Else
MsgBox "Unfortunately, the database does not have any sources for information in " & country & ". Please search for sources in relating to another country."
Sheets("Results").Range("D5").ClearContents
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
Exit Sub
End If
End With
2- If the user does not produce a name of a country to search for, the search will not run. I used an IF statement to do so:
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
End If
3 - If the user searches for a country in the database, the search runs, either listing all matches for the country or, if the user has narrowed the search by providing "Category" and "Subategory", listing only those database entries which match the three criteria. I have done this through an IF statement, separate from the first one:
For i = 2 To finalrow
'If the country field is filled in and there results from the search made
If 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
'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 "Database" 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
4 - As a last scenario, I want the script to react to the event where the user provides a "Country", a "Category" and/or a "Subcategory" but, although the database has entries for that country, it does not have entries for that country in that category or subcategory. In this case, I want the script to ask the user if it wants to see all information on the searched country, regardless of "Category" or "Subategory". I tried to do this through an IfElse statement, part of the previous IF statement:
ElseIf Sheets("Database").Cells(i, 1) = country And _
(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, "No results for your search")
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
MsgBox question
If question = vbYes Then
Close
Call SearchButton_Click
Exit Sub
Else
Sheets("Results").Range("D5").ClearContents
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
Exit Sub
End If
These are the four scenarios I want the script to account for as possible.
The search and the script as a whole are running fine when only scenarios 1, 2 and 3 are in place. However, when I insert the script of the ElseIf statement in scenario 4, something goes wrong. Although scenarios 1 and 2 keep on working, whenever I search for a country on which I know there are entries for "Category" and "Subcategory", the script prompts scenario 4 as if there were no matches for that "Country", "Category" and "Subcategory".
I am not sure what I might be doing wrong or what the problem is with my ElseIf statement.
Whole Code
Private Sub SearchButton_Click()
Dim country As String 'Search query category user-inputted
Dim Category As String 'Search query category user-inputted
Dim Subcategory As String 'Search query category user-inputted
Dim finalrow As Integer
Dim LastRowForTable As Long 'Last filled row in Results table
Dim i As Integer 'row counter
Dim ws As Worksheet
Set ws = Sheets("Database")
'Erase any entries from the Results sheet
Sheets("Results").Range("B10:J200000").ClearContents
'Define the user-inputed variables
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
'If statement for search
With Worksheets("Database")
Set c = .Range("A:A").Find(What:=country, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
Else
MsgBox "Unfortunately, the database does not have any sources for information in " & country & ". Please search for sources in relating to another country."
Sheets("Results").Range("D5").ClearContents
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
Exit Sub
End If
End With
'If the country field is left empty, there is no need to even run a search
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
End If
For i = 2 To finalrow
'If the country field is filled in and there results from the search made
If 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
'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
ElseIf Sheets("Database").Cells(i, 1) = country And _
(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")
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
MsgBox question
If question = vbYes Then
Close
Call SearchButton_Click
Exit Sub
Else
Sheets("Results").Range("D5").ClearContents
Sheets("Results").Range("D6").ClearContents
Sheets("Results").Range("D7").ClearContents
Exit Sub
End If
End If
Next i
'Hides search form
Me.Hide
'Toggle Results sheet
Sheets("Results").Activate
End Sub
finalRowandito aLonginstead of anInteger. That tends to be a run-time error waiting to happen. – SierraOscar