Recorded macros are always problematic: they make assumptions about the
context in which they are run, which might not be valid during playback.
They are really only good for repetitive keystroke sequences and for giving
you hints about how to *write* the macro.

Where date formats are not explicitly defined, Microsoft's standard practice
is to assume mm/dd/yy format unless the numbers are inconsistent with that
(as with 28/10/05, which *can't* be mm/dd/yy, and is therefore assumed to be
dd/mm/yy).

The answer is usually to clean up the macro code so it is explicit about
what it's doing.




"stephenb" wrote in
message ...

I recorded a macro to paste dates from a text file and the macro behaves
differently and gives different results then when I recorded it. The
macro stuffs up the dates which are pasted or stuffs up the date format
and I cannot fix the result.
The action is part of a larger macro, but I have attached a simplified
example which simulates the problem.
I have recorded the macro using macro record. While I record the result
is correct. However when I replay the macro the date returned can be
incorrect. Note my date format is d/mm/yy. The error only occurs for
dates with the day less than 13! (Something to do with the month range
1 to 12???)
eg: date copied is 1/11/05 the result is 11/1/2005, however the date
28/10/05 copies correctly. BUT I repeat the error does not occur during
live recording - it only occurs when playing the macro.
I have tested this on a second PC and it still occurs. I am using
English (Australia) for region. I tested English (US) and the error
still occurred.
I am interested to see if the same problem occurs for other users. You
could quickly try this example yourself.
I attach 2 files dates.txt and dates.xls Simply copy the dates in
dates.txt (to your clipboard), then within an excel file start
recording a macro, then simply paste the dates. Stop macro recording.
Now select another column and run the macro. Are the results the same?
They weren't for me!
dates.xls has a macro (macro1) already. It also shows the results I
got. All the macro does is
-ActiveSheet.Paste-
Cheers!


+-------------------------------------------------------------------+
|Filename: dates.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4031 |
+-------------------------------------------------------------------+

--
stephenb
------------------------------------------------------------------------
stephenb's Profile:
http://www.excelforum.com/member.php...o&userid=28726
View this thread: http://www.excelforum.com/showthread...hreadid=484187