Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
Use 2007 not 07
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
Hi,
Try this kind of construction Sub AutofilterDate() Dim MyDate, FindDate As Date MyDate = "13/12/2007" FindDate = Year(MyDate) & "/" & Month(MyDate) & "/" & Day(MyDate) Selection.AutoFilter field:=1, Criteria1:=FindDate End Sub Reijo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
You should move to the USA and just live with one date format <vvbg.
Ron de Bruin wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
You should move to the USA and just live with one date format <vvbg.
Do you have a room for me Dave <g -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... You should move to the USA and just live with one date format <vvbg. Ron de Bruin wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
Move to Canada. We'll take anybody<g
Gord On Sun, 16 Dec 2007 17:29:28 +0100, "Ron de Bruin" wrote: You should move to the USA and just live with one date format <vvbg. Do you have a room for me Dave <g |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
Move to Canada. We'll take anybody<g
LOL When I was 8 years old I visit Canada with my parents. It is a beautiful country Gord. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Move to Canada. We'll take anybody<g Gord On Sun, 16 Dec 2007 17:29:28 +0100, "Ron de Bruin" wrote: You should move to the USA and just live with one date format <vvbg. Do you have a room for me Dave <g |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in macros
And they're very polite!
Do you know how to get 20 Canadian's out of a pool? You ask them. Ron de Bruin wrote: Move to Canada. We'll take anybody<g LOL When I was 8 years old I visit Canada with my parents. It is a beautiful country Gord. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
How do I convert Dates from Text back to Date format using macros | Excel Programming | |||
Using dates in macros | Excel Programming |