I thought this was easy.....autofilter

[metropol]

Scanning this Forum, Ozgrid etc, I can't figure out what I have done wrong....The autofilter with use of a VARIABLE does not work. I have to open the autofilter in column 13 and just press "OK". Then the list appear. What is wrong??

Sub Datefilter()
   With Sheets("Sales")
          .AutoFilterMode = False
          .Range("A1:M1").AutoFilter
          .Range("A1:M1").AutoFilter Field:=1, Criteria1:="6"   
          .Range("A1:M1").AutoFilter Field:=10, Criteria1:="<100"
          .Range("A1:M1").AutoFilter Field:=13, Criteria1:="<=" & Date <-------Not working!!
   End With
End sub

I have tried to use the function Date. Also tried to assigned Date to a variable uDate instead. Still not working. Declaration of variables etc etc is still NOGO. The Date is correctly displayed in the autofilter dialog.

 

[Colin Legg]

Hi Metropol,

As we discussed on this thread:
http://www.mrexcel.com/forum/showthread.php?t=379467

You'll need to convert that date to a long data type.

Untested, but try:

.Range("A1:M1").AutoFilter Field:=13, Criteria1:="<=" & CLng(Date)

Hope that helps!

 

[metropol]

Hi Metropol,

As we discussed on this thread:
http://www.mrexcel.com/forum/showthread.php?t=379467

You'll need to convert that date to a long data type.

Untested, but try:

.Range("A1:M1").AutoFilter Field:=13, Criteria1:="<=" & CLng(Date)

Hope that helps!

Colin! Thank you.

Your way worked. I thought I did it like you said in the previous post, but my coding was not sufficient enough. Dont ask way

 

[Colin Legg]

You're welcome. It wasn't so obvious on that other thread because the Date data type --> Long data type conversion was done implicitly by assigning the date value in the cell to a long data type variable. Here, the Date() function returns a Variant (date) which we are passing directly into the autofilter, so we have to explicity convert it to a long.

Hope that makes more sense now.