Trouble with Subroutine - Works fine the first time it is called.

[rcbricker33]

I have a function that I am calling a few times. I simply change one of the variables (the first worksheet). Beyond that the function should do exactly the same thing each time it is called. The function is being used to generate a list of data from the first worksheet by filtering using an array. It then copies and moves the data to sheet two, pastes it to the sheet and then compares the list to create a new list which represents data found on sheet one that should be in the next step. I run the function and it works great the first time (for the first sheet that needs to be filtered). When it comes time to do it for the next sheet that needs to be filtered I am getting an error. I get an error number 9 Subscript out of range. Not sure how to get this to work. I need to run this function on 4 separate worksheets.

Function is called from the main sub with the following:

strFILTER = SUSfilter(wsUTBITD, wsMAP, arrSGL(), strFS, strLINE)

only the first worksheet variable is changed when the function is recalled.

Function SUSfilter(ws As Worksheet, wsMAP As Worksheet, arrSGL() As String, strFS As String, _
            strLINE As String)
Dim lotbl As ListObject
Dim locSGL As ListColumn, locSUS As ListColumn
Dim lng As Long, lngROW As Long, lngCOL As Long
Dim rng As Range, rngHEAD As Range, cell As Range, rngSUS As Range
Dim intMSUS As Integer, intMSGL As Integer, intMLINE As Integer
Dim str As String, strFILTER As String
Dim varI As Variant

    ws.Activate
    With ws
        Set lotbl = ws.ListObjects(1)
        lotbl.AutoFilter.ShowAllData
        Set locSGL = lotbl.ListColumns("CBDP_SGL")
        lng = locSGL.Range.Column
        Set locSUS = lotbl.ListColumns("CBDP_SUS")
        ws.ListObjects("AFS_TB").Range.AutoFilter Field:=lng, Criteria1:=arrSGL, Operator:=xlFilterValues
        Set rng = locSUS.Range
        rng.Copy
        lotbl.AutoFilter.ShowAllData
    End With
    wsMAP.Activate
    With wsMAP
        wsMAP.AutoFilterMode = False
        lngROW = LASTrow(wsMAP)
        lngCOL = LASTCOL(wsMAP)
        wsMAP.Cells(1, lngCOL + 2).PasteSpecial xlValues
        Set rngHEAD = wsMAP.Range(wsMAP.Cells(1, 1), wsMAP.Cells(1, lngCOL))
        If strFS = "BS" Then
            intMLINE = rngHEAD.Find("CBDP line").Column
        Else
            intMLINE = rngHEAD.Find("Line").Column
        End If
        intMSGL = rngHEAD.Find("SGL").Column
        intMSUS = rngHEAD.Find("Id").Column
        lng = wsMAP.Cells(wsMAP.Rows.Count, lngCOL + 2).End(xlUp).Row
        Set rng = wsMAP.Range(wsMAP.Cells(1, lngCOL + 2), wsMAP.Cells(lng, lngCOL + 2))
        Application.CutCopyMode = False
        wsMAP.Range(rng.Address).RemoveDuplicates Columns:=1, Header:=xlYes
        wsMAP.Sort.SortFields.Clear
        wsMAP.Sort.SortFields.Add Key:=wsMAP.Cells(1, lngCOL + 2), SortOn _
                :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        With wsMAP.Sort
            .SetRange rng
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        lng = wsMAP.Cells(Rows.Count, lngCOL + 2).End(xlUp).Row
        Set rng = wsMAP.Range(wsMAP.Cells(1, 1), wsMAP.Cells(lngROW, lngCOL))
        wsMAP.Range(rng.Address).AutoFilter Field:=intMLINE, Criteria1:=strLINE
        Set rng = wsMAP.Range(wsMAP.Cells(1, lngCOL + 2), wsMAP.Cells(lng, lngCOL + 2))
        Set rngSUS = wsMAP.Range(wsMAP.Cells(1, intMSUS), wsMAP.Cells(lngROW, intMSUS))
        For Each cell In rng
            lng = Empty
            str = cell.Value
On Error Resume Next
            lng = rngSUS.Find(What:=str, After:=wsMAP.Cells(1, intMSUS), LookIn:=xlValues, LookAt _
                :=xlWhole).Row
On Error GoTo 0
            If lng = 0 Then
                cell.ClearContents
            Else
                strFILTER = strFILTER & cell.Value & ","
            End If
        Next cell
        wsMAP.AutoFilterMode = False
        rng.Delete
        If Right(strFILTER, 1) = "," Then
            strFILTER = Left(strFILTER, Len(strFILTER) - 1)
        End If
        SUSfilter = strFILTER
    End With
End Function

Thanks in advance for any help.

 

[RoryA]

Which line causes the error?

 

[rcbricker33]

Which line causes the error?

my bad. I forgot to comment it.

following line causes the error. I used a for each and debugged the array. It is populated. It is also the same array used the first time it is called. This array doesnt change once it is populated.

ws.ListObjects("AFS_TB").Range.AutoFilter Field:=lng, Criteria1:=arrSGL, Operator:=xlFilterValues

 

[RoryA]

The error typically means that the table name doesn't exist on that sheet.

 

[rcbricker33]

That is exactly the reason. ****. I have a hardcoded table name in the line.

Thanks for the extra set of eyes.