Autofilter method of class range failed - arrays on listbox

[himjak]

I am new to VBA. Some help would be highly appreciated. I have two form control multiselect listboxes. I want to filter the source data worksheet based on selection in listbox 12 and listbox 11. The code is working fine when i multi select items from both listboxes and enter search. But I'm getting an error on array if i select either of listboxes. "Autofilter method of class range failed". Please advise

Sub Button15_Click()
'search
     Dim ws As Worksheet
     Set ws = Worksheets("Action Report")
     Dim wsa As Worksheet
     Set wsa = Worksheets("PMT Landing Page")
     Dim i1 As Long
     Dim j1 As Long
     Dim i As Long
     Sheet6.Select
    
Cnt = 1
With wsa.ListBoxes("List Box 12")
        For i1 = 1 To .ListCount
            If .Selected(i1) Then
             i1 = i1 + 1
            End If
        Next i1
End With

With wsa.ListBoxes("List Box 11")
        For j1 = 1 To .ListCount
            If .Selected(j1) Then
             j1 = j1 + 1
            End If
        Next j1
End With


If i1 > 0 Then


    Dim MyArray() As String
   
    Dim arr1 As Variant
   
    Cnt = 1
   
    With wsa.ListBoxes("List Box 12")
        For i = 1 To .ListCount - 1
            If .Selected(i) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = .List(i)
                    arr1 = MyArray(Cnt)
            End If
        Next i
    End With
   
    With ws
        .Visible = True
        .Select
        .Range("TableE").AutoFilter field:=10, Criteria1:=arr1, Operator:=xlFilterValues
    End With
 


End If



If j1 > 0 Then
   
Worksheets("PMT Landing Page").Activate
   
    Set wsa = Worksheets("PMT Landing Page")
    Cnt = 1
    Dim arr2 As Variant
   
    With wsa.ListBoxes("List Box 11")
  
        For i = 1 To .ListCount
            If .Selected(i) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = .List(i)
                    arr2 = MyArray(Cnt)
            End If
        Next i
   
    End With

    With ws
        .Visible = True
        .Select
        .Range("TableE").AutoFilter field:=11, Criteria1:=arr2, Operator:=xlFilterValues
    End With
  
    
          
End If




End Sub

 

[DanteAmor]

Hi and welcome to MrExcel.

I have Excel 2007 for windows. I see that the syntax is similar in almost everything. Maybe what you should change is the way to use the listbox.

With wsa.ListBox12 by With wsa.ListBoxes("List Box 12")

In the following way it works for me:

Sub Button15_Click()
  Dim wsa As Object, cnt12 As Long, cnt11 As Long, i As Long
  Dim MyArray12(), MyArray11()
  
  Set wsa = Worksheets("PMT Landing Page")
  
  cnt12 = 0
  With wsa.ListBox12
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve MyArray12(cnt12)
        MyArray12(cnt12) = .List(i)
        cnt12 = cnt12 + 1
      End If
    Next i
  End With
  
  cnt11 = 0
  With wsa.ListBox11
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve MyArray11(cnt11)
        MyArray11(cnt11) = .List(i)
        cnt11 = cnt11 + 1
      End If
    Next i
  End With
  
  With Sheets("Action Report")
    .Range("A1").AutoFilter
    If cnt12 > 0 Then .Range("TableE").AutoFilter 10, MyArray12, xlFilterValues
    If cnt11 > 0 Then .Range("TableE").AutoFilter 11, MyArray11, xlFilterValues
  End With
End Sub