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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,029
Messages
5,526,340
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top