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

 

[Fluff]

You can slim that code down like

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")
   
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, 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"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub

I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive.

 

[KatKitKat]

You can slim that code down like

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")
  
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, 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"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub

I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive.

I will try this. I am working from a spreadsheet someone else started so it is possible that are spaces or something funky happening. I will look again. Thank you.

 

[Jeffrey Mahoney]

Hello, I was able to get mine to work, bet then I don't have your workbook. I couldn't help but try to improve the code a little. You should debug the code line by line and see when your code stops working.

Sub Button37_Click()
  Dim list1 As String, list2 As String
  Dim Sht As Worksheet
   
  ActiveSheet.Unprotect Password:="1234"
  Set Sht = Sheets("ExposureLog")
  list1 = Range("F3").Value
  list2 = Range("G3").Value
  
  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:=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 If
  End With
    
  ActiveSheet.Protect Password:="1234"
End Sub

 

[KatKitKat]

Hello, I was able to get mine to work, bet then I don't have your workbook. I couldn't help but try to improve the code a little. You should debug the code line by line and see when your code stops working.

Sub Button37_Click()
  Dim list1 As String, list2 As String
  Dim Sht As Worksheet
  
  ActiveSheet.Unprotect Password:="1234"
  Set Sht = Sheets("ExposureLog")
  list1 = Range("F3").Value
  list2 = Range("G3").Value
 
  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:=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 If
  End With
   
  ActiveSheet.Protect Password:="1234"
End Sub

Thanks, I am wondering if there is a data issue somewhere that is causing this. Back to the drawing board. Thank you for your help.

 

[Fluff]

I will try this.

If it doesn't work, try stepping through the code using F8 & see if the code enters into the If statements, or just jumps past them.

 

[KatKitKat]

You can slim that code down like

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")
  
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, 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"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub

I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive

 

[KatKitKat]

I will try this. I am working from a spreadsheet someone else started so it is possible that are spaces or something funky happening. I will look again. Thank you.

I used your coding and got an Autofilter method... error message. I had the print range filtered and that prevented the range from doing what I needed. Took that previous filter off and then your code worked! I must have a typo in one of the selections in list 2 that caused the problem. Thank you again! I am learning so much from this site. Have a wonderful day! Kat

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[KatKitKat]

Glad we could help & thanks for the feedback.

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.