Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

Custom filter date using VB

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kevin - 29 Mar 2008 18:46 GMT
Hi

I am running a macro to select rows from data using filter of certain
fields. It works fine for the fields where I have to searching for specific
text eg

Selection.AutoFilter Field:=10, Criteria1:="=*" & strReportSponsor & "*"

however I am trying to filter a date field to say where the date is less
then a particular date

Selection.AutoFilter Field:=13, Criteria1:="<=" & ReportDueBy

the macro enters the less than date in the filter but it does not select any
rows. When I select the filter manually I can see the date has been entered
by the macro and then when I manually select "ok" again it works fine. Any
idea why the macro is not returning any rows?

Signature

Kevin

Dave Peterson - 29 Mar 2008 20:05 GMT
Ron de Bruin pointed me to a way that works for him (he can have a problem with
international issues).

This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgRef/Ch22/ProgRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

  Range("A1:D200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"

==============
So in your case, I'd try this first:

Selection.AutoFilter Field:=13, _
    Criteria1:="<=" & format(ReportDueBy,"mm/dd/yyyy")

> Hi
>
[quoted text clipped - 16 lines]
> --
> Kevin

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.