Need some help with Autofilter VBA

[AMAT]

Hello,

My key strokes to record the macro are:
- Ctrl-Home
- Ctrl-F "platform"
- Alt-Downarrow "reflexion"
- Enter

The resulting VBA is:
Range("A1").Select
Cells.Find(What:="platform", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveSheet.Range("$G$1:$P$46").AutoFilter Field:=7, Criteria1:= _
"=REFLEXION", Operator:=xlOr, Criteria2:="=REFLEXION LK"


I want the VAB to do the autofilter on the column that it found the word "platform" in row 1, not on the 7th column from the left (Field:=7).

Thanks!

 

[RickXL]

Hi,

One way would be something like this:

Sub AF()
    Dim r  As Range
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("$G$1:$P$46")
            Set r = .Find(what:="platform", after:=.Range("G1"))
            .AutoFilter Field:=r.Column - .Column + 1, Criteria1:= _
                "=REFLEXION", Operator:=xlOr, Criteria2:="=REFLEXION LK"
        End With
    End With
End Sub

 

[AMAT]

Hi,

One way would be something like this:

Sub AF()
    Dim r  As Range
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("$G$1:$P$46")
            Set r = .Find(what:="platform", after:=.Range("G1"))
            .AutoFilter Field:=r.Column - .Column + 1, Criteria1:= _
                "=REFLEXION", Operator:=xlOr, Criteria2:="=REFLEXION LK"
        End With
    End With
End Sub

You Sir, are Awesome!

Thank You Very Much!

 

[RickXL]

No problem.

I am glad you appreciate it.

Regards,

 

[manojparag]

Hi RickXL,

What if we want to search more than one word. If we have around 10 word and we want to autofilter all 10 word one by one.
How would we write a code?