Macro for autofilter using variables declared in worksheet

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi,
I have recorded the following macro which works fine

Sub Filter1()
'
' FilterbyProduct Macro
' Basis for Filter based on criteria as a range entered in sheet
'
Selection.AutoFilter Field:=3, Criteria1:=">=15", Operator:=xlAnd, _
Criteria2:="<=20"
Selection.AutoFilter Field:=2, Criteria1:="=10", Operator:=xlAnd
Range("C3").Select
End Sub

I always intended for the variables to actually be declared in the worksheet
because the filters are always based on (first filter) groups of products in
the format ">=x" and "<=y", with the second filter "=z".
I haven't been able to substitute the value of the criteria contained in
cells Main!A1,A2 and A3 instead of the "fixed" values in the recorded macro
i.e 15,20 and 10 (shown above).

Can anyone help.
sincerely
Jeff
 
Change the criterial portion from this:
Criteria1:=">=15"
to:
Criteria1:=">"&worksheets("main").range("a1").value
 
Many thanks. I'm impressed. I spent hours trying different combinations of
things.
You (and other poster repliers) provide much valued education value to us
ordinary excel users.
sincerely

Jeff
 
Back
Top