mardi 22 novembre 2016

Find and If Statements in Excel VBA

I am trying to write a script where a search is run through an Excel database which is in a Sheet called "Database". The search results are then to be presented in a sheet called "Results". The search can be made according to certain criteria inputted by the user, namely the fields "Country", "Category" and "Subcategory". These are defined as user-dependent variables as follows:

country = Sheets("Results").Range("D5").Value
Category = Sheets("Results").Range("D6").Value
Subcategory = Sheets("Results").Range("D7").Value

I want the "Country" field to be filled mandatorily, in order for a search to run. If this condition is verified, then the search will run according to the remaining criteria provided by the user. I have written the following in order for this to happen:

finalrow = Sheets("Database").Range("A200000").End(xlUp).Row
For i = 2 To finalrow

    'If the country field is left empty
    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 country field is filled in and there results from the search made
    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

            'Copy the headers of the table
            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

    'Hides search form
    Me.Hide

    End If

Like this, the script and search are running fine.

I now want to take into consideration the event in which the user fills the "Country" field but the value entered does not match anything in the database, i.e., the database contains no information on the country searched. I was advised to do this by inserting a .Find statement before the already written If statement in order for the script to check, first, if the country is in the database and only after continue with the search. Being a beginner in coding, I searched online and this is what I ended up writing:

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
    MsgBox "Unfortunately, the database does not have any information regarding the country you specified. Please search for information on another country."
        Exit Sub
    End If
End With

This is not working. The script still presents the results whenever I input a country in the database. However, whenever I leave the "Country" field blank, it stopped returning MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided." and whenever I input an inexistent country it just presents the "Results" sheet bearing no results.

How can I fix the issue? Thank you very much for your help.

Aucun commentaire:

Enregistrer un commentaire