Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter quits operating | Excel Discussion (Misc queries) | |||
Dates not entered correctly | Excel Discussion (Misc queries) | |||
Text to Columns not working correctly with dates in Excel 2007 | Excel Discussion (Misc queries) | |||
Autofilter not working correctly... | Excel Discussion (Misc queries) | |||
AutoFilter toggling in VBA (How to use correctly) | Excel Discussion (Misc queries) |