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
精彩评论