Macro for autofilter using variables declared in worksheet

Change the criterial portion from this:
Criteria1:=">=15"
to:
Criteria1:=">"&worksheets("main").range("a1").value



Jeff wrote:
>
> 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

--

Dave Peterson

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Change the criterial portion from this:
> Criteria1:=">=15"
> to:
> Criteria1:=">"&worksheets("main").range("a1").value
>
>
>
> Jeff wrote:
>>
>> 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
>
> --
>
> Dave Peterson

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



Jeff wrote:
>
> 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

--

Dave Peterson

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Change the criterial portion from this:
> Criteria1:=">=15"
> to:
> Criteria1:=">"&worksheets("main").range("a1").value
>
>
>
> Jeff wrote:
>>
>> 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
>
> --
>
> Dave Peterson