variable criteria for autfilter

[methode2404]

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

 

[Col Delane]

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") ]

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?