Hi Dave

Good morning

Thanks for posting the notes I send you (will remember to do it also the next time)

I send a bug report yesterday about it.

If you record a macro when you do it manual the macro recorder record the system
Date format and that is not working when you have another Date format as US on your system.
You must change it in the US date format MM/DD/YYYY then

Confusing

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave Peterson" wrote in message ...
Just to add to Ron's response...

Ron sent me this message in a private email a few months ago:

See also Stephen his Autofilter notes in this PDF
http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm

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"



Ron de Bruin wrote:

Hi Max

Change the date format in the code to US format

MDY

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Max Scott" wrote in message ...
Hi

I have a problem with a filter in a macro. I recorded a macro and it gave
following code:

selection.autofilter field:=1, Criteria1:="13/12/07"

(This is date type dd/mm/yy by the way)

But when I change the criteria1 to "14/12/07" and run the macro it does not
filter correctly.

Is there something special I need to do with dates?

Thanks

Max




--

Dave Peterson