VBA - Message Box is Criteria not available

[KatKitKat]

Hi.

I need some help on if something can be done. I have a spreadsheet with many rows of data. I added a macro that users can select 2 criteria to filter the list by selecting choices from drop down fields and "clicking" a button. This works fine. My problem is if there is no data in the listing based on the selected option combination. When this happens, the report does not filer. I would assume if there is no data in the list, the report would show nothing but instead it just doesn't filter and the user does not know that there is entries that match the requested filter.

Can I add a command that if the selected combination is not available, a message box would pop up saying "There are no entries that match this criteria. Try Again"? Below is the beginning and end of my existing code (several lines long so I will spare all the coding).

Thanks for any help offered.
Kat

Sub Button24_Click() 'filter Use Log by SOF and Status
    ActiveSheet.Unprotect Password:="1234"
    Dim list1 As String, list2 As String
    
    list1 = Range("D2")
    list2 = Range("E2")
        
    If list1 = "Construction Contingency" And list2 = "USED" Then
        With Worksheets("UseLog").Range("A11:J1010")
        .AutoFilter Field:=5, Criteria1:="Construction Contingency"
        .AutoFilter Field:=6, Criteria1:="Closed"
        Range("A7") = Sheets("ExposureLog").Range("K2:K2") & (" - ") & Range("D2") 'set for all transactions
        Range("I6") = Sheets("Table").Range("E68") 'set for construction contingency
        Range("I7") = Sheets("Table").Range("E69") 'set for construction contingency
        Range("I8") = Sheets("Table").Range("E70") 'set for construction contingency
        Range("A9") = Range("E2") & (" ") & ("CONTINGENCY TRANSACTIONS") 'set for all transactions - may need IF statement to add note that pending means open
        Range("G1993") = Range("E2") & (" ") & ("Total:")
        End With
        ElseIf list1 = "Construction Contingency" And list2 = "PENDING" Then

Code contines on with all possible combinations and ends as such:

  End With 'END OF PROJECT CONTINGENCY
        End If
    ActiveSheet.Protect Password:="1234"
End Sub

 

[Anthony47]

That did it! Whew! I am so grateful for your help. Have the best day!

Thank you for the feedback
Don't miss @Joe4 message on using Option Explicit; in our case it is not sufficient to declare the variable, it needs to be declared "as Range" for the algorithm to work properly