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]

You might check the filtered range using the same technique I suggested here:

Macro not finding filtered value, copying whole data unfiltered. How to prevent this?

Good morning guys, If my macro doesn't find the filtered value, it is copying all the data which is wrong. How to prevent this? It should either just resume next i value for next filter value or just delete the template lines, leaving row 1 with the headers. My loop code is as follows: For i...

www.mrexcel.com

Thus:

Dim Rng As Range
'..
'..
'..
'..

    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"
'added check >>:
            On Error Resume Next
                Set Rng = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Rng Is Nothing Then
                '
                'What to do if there are no cells??
                '
            End If
'<< end added check
            Range("A7") = Sheets("ExposureLog").Range("K2:K2") & (" - ") & Range("D2") 'set for all transactions
            'more code

 

[KatKitKat]

Thank you Anthony, but it did not work. I added a message box where you noted, "what to do if..." but.nothing popped up.

 

[Anthony47]

If Range("A11:J1010") include the header in row 11 then you need to use, instead of Set Rng = .SpecialCells(xlCellTypeVisible)

Set Rng = Worksheets("UseLog").Range("A12:J1010").SpecialCells(xlCellTypeVisible)

 

[KatKitKat]

HI Anthony,
Sorry for the delayed response.

I am also sorry to be such a pest but this isn't quite working either. What is happening is when there is no data in the criteria with adding this piece of code, the file filters so data is shown, which I am ok with, but the message doesn't pop up. I get a "Run Time Error 474 - Object Not Found" and the "If Rng Is Nothing Then" is highlighted. Very frustrating because I am sure it is a simple syntax error and I can't see it. Below is where the code is. I added to part of the code where I know there is no data.

Also, row 10 is the header row with data to filter starting in row 11.

Kat

ElseIf list1 = "Furniture& Fixtures Contingency" And list2 = "PENDING" Then
        With Worksheets("UseLog").Range("A10:J1010")
        .AutoFilter Field:=5, Criteria1:="Furniture& Fixtures Contingency"
        .AutoFilter Field:=6, Criteria1:="Open"
        On Error Resume Next
            Set Rng = Worksheets("UseLog").Range("A11:J2510").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Rng Is Nothing Then
            MsgBox ("There are no exposures that match the selected criteria. Please try again."), vbExclamation
        End If
        Range("A7") = Sheets("ExposureLog").Range("K2:K2") & (" - ") & Range("D2") 'set for all transactions
        Range("I6") = Sheets("Table").Range("G68") 'set for Furniture& Fixtures Contingency
        Range("I7") = Sheets("Table").Range("G69") 'set for Furniture& Fixtures Contingency
        Range("I8") = Sheets("Table").Range("G70") 'set for Furniture& Fixtures Contingency
        Range("A9") = Range("E2") & (" ") & ("CONTINGENCY TRANSACTIONS") 'set for all transactions 
        Range("G1993") = Range("E2") & (" ") & ("Total:")
        End With

 

[Anthony47]

Did you declare Rng in your code?

Dim Rng As Range

 

[KatKitKat]

No I didn't. Not sure where this goes. The beginning of the code does the following:

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")

Where would the Dim Rng As Range code go?

Thank you so much for your help and your patience. I am new to this and learning as I go, but get stuck on alot of the nuances of VBA that I don't have experience with as of yet.

K

 

[Anthony47]

The best position for declaring the variable is at the beginning of the procedure; thus:

    Dim list1 As String, list2 As String
    Dim Rng As Range                             '<<< ADD THIS LINE HERE
    
    list1 = Range("D2")
'more code

 

[KatKitKat]

The best position for declaring the variable is at the beginning of the procedure; thus:

    Dim list1 As String, list2 As String
    Dim Rng As Range                             '<<< ADD THIS LINE HERE
   
    list1 = Range("D2")
'more code

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

 

[Joe4]

Yes, best practice usually says it is best to declare all variables at the very top of your code, so they can easily all be seen in one place.

I would also recommend to turning on "Option Explicit" to force you to declare all your variables before using them.
It helps with debugging, in quickly identifying typos and ensuring the variables are being used correctly.
See: Option Explicit in Excel VBA