VBA - Apply Filter With Partial Header Match

Question

I posted a question for a macro solution to applying a filter across multiple worksheets based on header match.

Link: Loop Through Multiple Worksheets and Apply Filter

I require help with a partial header match and not just an exact match, because, some of the headers will not be an exact match for the criteria - i.e, some will have "STATUS" as the header, some will be "prefix_Status", others "CurrentStatus" and so on.. So I need to use the Instr function (Unless there's some better option) to find any header that contains the word STATUS but I cannot seem to figure out where or how to use it..

I have the current solution which throws the following error on the Match line:

Type Mismatch

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.count

         ' Begin the loop.
         For I = 1 To WS_Count

            Dim count As Integer, rngData As Range

            With Worksheets(I)
                Set rngData = .Range("A1").CurrentRegion

                count = Application.Match("*STATUS*", Worksheets(I).Range("A1:AZ1"), 0)

                If Not IsError(count) Then
                    rngData.autofilter Field:=count, Criteria1:="INACTIVE"
                End If

            End With

         Next I

End Sub

Is it possible there is no match? See [this post](https://stackoverflow.com/q/27302794/2521004) for more info — Automate This, Mar 28 '18 at 14:04
It is quite possible, but if I try convert the Int which is "I" to a str then i get "subscript out of range". — Eitel Dagnin, Mar 28 '18 at 14:14

score 1 · Accepted Answer · answered Mar 28 '18 at 14:43

Application.Match returns an error when no match is found. Your using IsError to check for errors but the code is stopping before that point. You need to skip the errors so that you can catch them later in code.

Try adding: On Error Resume Next

Full Code:

Sub WorksheetLoop()
    Dim WS_Count As Integer, i As Integer, count As Integer
    Dim rngData As Range

    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.count

    'If Application.Match finds no match it will throw an error so we need to skip them
    On Error Resume Next

    ' Begin the loop.
    For i = 1 To WS_Count
       With Worksheets(i)
           count = Application.Match("*STATUS*", Worksheets(i).Range("A1:AZ1"), 0)

           If Not IsError(count) Then
               Set rngData = .Range("A1").CurrentRegion
               rngData.AutoFilter Field:=count, Criteria1:="INACTIVE"
           End If
       End With
    Next i
End Sub

Tested on simple data set

Before:

After:

Perfect! Thank you Portland Runner :) – Eitel Dagnin Mar 29 '18 at 08:09 — Eitel Dagnin, Mar 29 '18 at 08:09

VBA - Apply Filter With Partial Header Match

1 Answers1