Excel VBA Autofilter on dates not operating correctly
 

I have a list of records with a date column I wish to autofilter o
using a macro. In one cell outside the list I have the =TODAY(
formula (in position 4,7 in sheet) and I wish to filter the record
with the criteria <today. Using the following code the filter return
no results when the macro is run. However, when I go in to th
(custom) autofilter for that column it shows the "less than" criteri
and the date value as expected. If I click in the date value field an
hit RETURN the filter then correctly filters. I am guessing this is du
to date data type as if I want a simple equals value it works fine.
However, in order to concatenate the string of "<" + date I need to us
the string format.

Sub overdue()

Dim today As String

today = "<" & Worksheets(1).Cells(4, 7).Value

MsgBox (today)

Selection.AutoFilter Field:=13, Criteria1:=today, Operator:= _
xlAnd


End Sub


I am using Excel 2002. I have tried formatting the 4,7 and date colum
in various date and number formats.

How can I get the filter to work correctly automatically

--
Message posted from http://www.ExcelForum.com

Excel VBA Autofilter on dates not operating correctly
 

Sometimes, if you convert the date to a Long integer, it'll work--sometimes not.

things to try:
today = "<" & Clng(Worksheets(1).Cells(4, 7).Value)
or
today = "<" & clng(Worksheets(1).Cells(4, 7).Value2)

And this won't have any effect on your code, but you can get today's date in VBA
via:

today = "<" & clng(date)



"99flake <" wrote:

I have a list of records with a date column I wish to autofilter on
using a macro. In one cell outside the list I have the =TODAY()
formula (in position 4,7 in sheet) and I wish to filter the records
with the criteria <today. Using the following code the filter returns
no results when the macro is run. However, when I go in to the
(custom) autofilter for that column it shows the "less than" criteria
and the date value as expected. If I click in the date value field and
hit RETURN the filter then correctly filters. I am guessing this is due
to date data type as if I want a simple equals value it works fine.
However, in order to concatenate the string of "<" + date I need to use
the string format.

Sub overdue()

Dim today As String

today = "<" & Worksheets(1).Cells(4, 7).Value

MsgBox (today)

Selection.AutoFilter Field:=13, Criteria1:=today, Operator:= _
xlAnd

End Sub

I am using Excel 2002. I have tried formatting the 4,7 and date column
in various date and number formats.

How can I get the filter to work correctly automatically?

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson