Filter code not working

[KatKitKat]

hi guys,

I am stumped on why my code is not working and could use another set of eyes to look and possibly see where my mistake is. I have a spreadsheet that I want to filter using a command button. I have 2 fields to select the filter. I have drop down lists in cells F3 and G3 that the user will make their selections then hit the button for the spreadsheet to be filtered on both criteria. Below is the start of the code (there can be 24 combinations of selections when complete). When I test this, nothing happens. The sheet does not filter and no error message, which makes me think I have done something stupid and just can't see it. Any input would help. Thank you!

Kat

Sub Button37_Click() 'filter Exp Log by Impact Cost and Status
    ActiveSheet.Unprotect Password:="1234"
    Dim list1 As String, list2 As String
   
    list1 = Range("F3")
    list2 = Range("G3")
       
    If list1 = "Open" And list2 = "1 Construction" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="1 Construction"
        End With
        ElseIf list1 = "Open" And list2 = "2 Architectural & Engr" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="2 Architectural & Engr"
        End With
        ElseIf list1 = "Open" And list2 = "3 FF&E" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="3 FF&E"
        End With
        ElseIf list1 = "Open" And list2 = "4 Security & Equipment" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="4 Security & Equipment"
        End With
        ElseIf list1 = "Open" And list2 = "5 One Time Expenses" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="5 One Time Expenses"
        End With
        ElseIf list1 = "Open" And list2 = "7 Program Staffing" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="7 Program Staffing"
        End With
        ElseIf list1 = "Open" And list2 = "8 Program Artwork" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="8 Program Artwork"
        End With
        ElseIf list1 = "Open" And list2 = "P PROGRAM COST" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="P PROGRAM COST"
        End With
        ElseIf list1 = "Open" And list2 = "ALL" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:=Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), _
        Operator:=xlFilterValues
        End With        'End of OPEN
        End If
    ActiveSheet.Protect Password:="1234"
End Sub

 

[KatKitKat]

The "ALL" option isn't filtering so I think that is where there are data mismatches. Is there a way to say if ALL is selected, don't filter on this field? Those are all the options available.

Never mind. I found the typo!

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[Jeffrey Mahoney]

I think you should use a table of values instead of providing a string array in the code. I created a List2 Table so I could edit the values any time and then added code to grab the values from that table

Sub Button37_Click()
  Dim list1 As String, list2 As String
  Dim Sht As Worksheet
  Dim Ary As Variant
  Dim Rng As Range
  Dim Cel As Range
  Dim X As Long
  Dim Q As String
   
  ActiveSheet.Unprotect Password:="1234"
  Set Sht = Sheets("ExposureLog")
  list1 = Range("F3").Value
  list2 = Range("G3").Value
  [COLOR=rgb(184, 49, 47)]Set Rng = Sheets("Sheet4").ListObjects("List2").ListColumns(1).DataBodyRange
  Ary = Application.Transpose(Rng.Value2)[/COLOR]
  
  On Error Resume Next
  Sht.ShowAllData
  On Error GoTo 0
  
  If list1 <> "Open" Then Exit Sub
       
  With Sht.Range("A7:S2508")
    If list1 <> "" Then .AutoFilter Field:=13, Criteria1:=list1
    If list2 <> "" And list2 <> "All" Then
      .AutoFilter Field:=6, Criteria1:=list2
    ElseIf list2 = "All" Then
      .AutoFilter Field:=6, Criteria1:=Ary, Operator:=xlFilterValues      
    End If
  End With
    
'  ActiveSheet.Protect Password:="1234"
End Sub