CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 304

How can I autofilter when I'm not sure which column it will be?

I'm trying to automate the processing of various reports, which I just need to filter and count the rows of. I currently have a PowerShell script that opens each report and runs a few macros. It is just about in a working state, but I'd now like to make it a bit more intelligent and catch some of the fails, so I might be asking a few questions but I'll stick to the one problem on each.

The reports have similar, but not identical layouts. I am looking for a particular column name to then autofilter. I have a very basic and bodged together macro that currently does this, and works (most of the time) for example, sometimes the column I want is A or B: 

If Worksheets(1).Range("A1") Like "*word" Then
    Worksheets(1).Range("A1").AutoFilter Field:=1, Criteria1:="=criteria1", Operator:=xlOr, Criteria2:="=criteria2"

ElseIf Worksheets(1).Range("B1") Like "*word" Then
    Worksheets(1).Range("A1").AutoFilter Field:=2, Criteria1:="=criteria", Operator:=xlOr, Criteria2:="=criteria2"

Hopefully that gives you the current picture.

I now want to instead, do a search for the field header I am looking for, then filter that column, so if the report format in future changes my macro won't break. Something similar to:

ColNum = Application.Match("*header", Range("A:Z"), 0)
ColNumInt = CInt(ColNum)

If ColNumInt > 0 Then
    ActiveSheet.Range("A1").AutoFilter Field:=ColNumInt, Criteria1:="=criteria1*", Operator:=xlAnd
End If

But this gives an error "AutoFilter method of Range class failed", Googlefu says to turn off filters but they're already off. So I'm a bit stuck.

Upvotes: 1

Views: 134

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

This should work for you.

Sub Button1_Click()
    Dim r As Range
    Dim c As Integer

    Set r = Range("A1:B1").Find(what:="*word*", lookat:=xlWhole)
    c = r.Column
    ActiveSheet.AutoFilterMode = 0

    Columns(c).AutoFilter Field:=1, Criteria1:="*criteria1*"
End Sub

Upvotes: 1

Rory
Rory

Reputation: 34065

This part will always fail:

ColNum = Application.Match("*header", Range("A:Z"), 0)

since match only works on one row or column. So your code is actually returning Error 2042, which is then converted to 2042 by CInt. I guess you don't have that many columns of data, hence the autofilter fails. Use:

ColNum = Application.Match("*header", Range("A1:Z1"), 0)
If Not IsError(ColNum) Then
...
End If

Upvotes: 2

Related Questions