variable criteria for autfilter

methode2404

New Member
Joined
Sep 3, 2018
Messages
30
Hi,

It doesn't work,
It should filter the data in sheet(data) with the criteria in "A4:A42" when i input RngSun
It should filter the data in sheet(data) with the criteria in "B4:B40" when i input RngMon.....

Where is the mistake ???

Sub testautofilter()


Dim RngSun As Range
Dim RngMon As Range
Dim RngTues As Range
Dim RngWed As Range
Dim RngThurs As Range
Dim RngFri As Range




Set RngSun = Sheets("sevk").Range("A4:A42")
Set RngMon = Sheets("sevk").Range("B4:B40")
Set RngTues = Sheets("sevk").Range("C4:C39")
Set RngWed = Sheets("sevk").Range("D4:D39")
Set RngThurs = Sheets("sevk").Range("E4:E39")
Set RngFri = Sheets("sevk").Range("F4:F23")


INPPP = InputBox("enter your choice")




ActiveSheet.Range("$A$1:$L$100").AutoFilter field:=5, Criteria1:=INPPP, Operator:=xlFilterValues


End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
1. Is "sheet(data)" in the following a sheet named "Data", and is it the active sheet when you run the code?
It should filter the data in sheet(data) ...
If so, your autofilter range Sheets("Data").Range("$A$1:$L$100") and your criterion range are in different sheets and ranges, and I'm not sure that Excel can handle that. You can't do it directly in the Excel interface (i.e. without using VBA - though there are many such things!)


2. What values are in your criteria ranges Sheets("sevk").Range("Ref:Ref")?
Do these exist in ActiveSheet.Range("$A$1:$L$100").AutoFilter field:=5?


3. I could be wrong but don't think the Criteria1 argument in your Autofilter code can accept a range object [ Sheets("sevk").Range("Ref:Ref") ]
Code:
ActiveSheet.Range("$A$1:$L$100").AutoFilter field:=5, [COLOR=#ff0000][B]Criteria1:=INPPP[/B][/COLOR], Operator:=xlFilterValues


4. What actually happens (& doesn't happen as you expect) when you run the code?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,877
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top