开发者

Macro button to change filter

I want to have buttons to change my excel filter.

I filter my fields with contains=something so each button should change that something text.

Button1: change filter to contain开发者_Go百科s=button1

Button2: change filter to contains=button2

and so on..


Since I found answer myself, I put it here for future help.

    Sub AI()

    '            sheet range and which field you want to filter and criteria to search
        ActiveSheet.Range("$A$2:$Z$203").AutoFilter Field:=14, Criteria1:="stringtomatch"
    End Sub


You could make it easier by referencing the the filter search string from a cell as follows.

Sub Filter()
    Dim searchField As String

    searchField = "=*" & Range("H2") & "*"

    ActiveSheet.Range("$A$3:$H$18401").AutoFilter Field:=8, Criteria1:= _
        searchField, Operator:=xlAnd

End Sub


I would add a HLOOKUP in e.g. cell h2 that would be controlled by the spin button changing field F1. in this case we have a full solution

 =HLOOKUP(h2;h2:h100;F1;0)

This is combined with this macro provided above:

Sub Filter()
     Dim searchField As String

     searchField = "=*" & Range("H2") & "*"

    ActiveSheet.Range("$A$3:$H$18401").AutoFilter Field:=8, Criteria1:= _
        searchField, Operator:=xlAnd

 End Sub
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜