How to filter by date ?

[franksirvent]

Hi, I have a macro which filters the excel form based on values entered on certain cells
However I am unable to filter between two dates.

The routine below works well, however I need the 20/07/2006 to be a variable (which is foundon CELL D4)

Selection.AutoFilter Field:=2, Criteria1:="<=20/07/2006", Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=">=20/07/2006", Operator:=xlAnd

I have substituted the 20/07/06 with the following code but doesn't work..

The_date = Range("D4").Value
Selection.AutoFilter Field:=2, Criteria1:="<="" & The_date & "", Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=">="" & The_date & "", Operator:=xlAnd

Any ideas?????

 

[KenPuls]

Just at first glance, it looks like you've got some extra quotes in there:

The_date = Range("D4").Value
Selection.AutoFilter Field:=2, Criteria1:="<=" & The_date & ", Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=">=" & The_date & ", Operator:=xlAnd

Ken Puls, CMA

http://www.excelguru.ca

 

[franksirvent]

I AM SORRY ken but i get an additional " added at the end automatically like this:

Selection.AutoFilter Field:=2, Criteria1:="<=" & The_date & ", Operator:=xlAnd"
Selection.AutoFilter Field:=3, Criteria1:=">=" & The_date & ", Operator:=xlAnd"

 

[KenPuls]

LOL!

Uh, yeah. That's cause I missed those.

That should be correct, I believe. Does it work in that fashion?

Ken Puls, CMA

http://www.excelguru.ca

 

[franksirvent]

Sorry but it doesn't work, I believe there shouldn't be any " at the end of the line

 

[KenPuls]

Sorry, you're correct.

I was looking at your code, and assumed that it worked like placing a formula. I'm fooling with a mockup now...

Try this:

The_date = Range("D4").Value
Selection.AutoFilter Field:=2, Criteria1:="<=" & The_date , Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=">=" & The_date , Operator:=xlAnd

Ken Puls, CMA

http://www.excelguru.ca

 

[franksirvent]

sorry mate, that doesn't work either.

 

[KenPuls]

What error are you getting?

Here's how I mocked up my test. I changed your code just a little to remove any active autofilter first:

Sub test()
Dim The_date As Long
The_date = Range("D4").Value
With Selection
    .Parent.AutoFilterMode = False
    .AutoFilter
    .AutoFilter Field:=2, Criteria1:="<=" & The_date, Operator:=xlAnd
    .AutoFilter Field:=3, Criteria1:=">=" & The_date, Operator:=xlAnd
End With
End Sub

Can you adapt that and let me know?


Ken Puls, CMA

http://www.excelguru.ca

 

[franksirvent]

I am trying to understand your code and see if I can change it on my sheet but I can't
You are using With Selection etc which I am not using and also don't know how to use

I have a sheet with a filter, so I don't select any cells, I just run the code on the Worksheet_SelectionChange routine

Whenever I change a cell (there is a total of 5 that can be changed) it automatically runs the filtering code...but unfortunately the date fields/columns don't seem to work...
The date columns are the only ones that don't work, probably because dates are handled 'different' which is what I am trying to find out...

thanks for your input

 

[KenPuls]

Truly, the only difference in the dates is that they are actually treated like numbers, not the textual representation that you see on screen. If you're picking it up from the cell though, providing it is a real date there (and not text), it should be fine.

If you format the date in cell D4 as a number, does it change to a number? If not, that's your issue.

Sorry, but I have to head to bed now. I'll check back on you in the morning though.

Ken Puls, CMA

http://www.excelguru.ca

 

[SkipVought]

Are the values in your table REAL DATES?

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_[red][/red]