Date not Saving As Expected

[Kdub via OfficeKB.com]

I have this code in a VBA module:

Rows(1).Insert
Range("A1").Value = "H"
Range("B1").Value = SheetName
Range("C1").Value = Date
Range("C1").NumberFormat = "m/dd/yyyy"
Range("C1").NumberFormat = "@"

When debugging, it returns the correct date,
yet when I view the modified worksheet, this
date is displayed as a number, then stored as
one. How can I ensure that it is saved in m/dd/yyyy
format? I've tried this with and without that last NumberFormat
statement with the same result.

Kevin

 

[Guest]

hi Kdub
you have a wrong line
try this

:
Rows(1).Insert
Range("A1").Value = "H"
Range("B1").Value = SheetName
Range("C1").Value = Date
Range("C1").NumberFormat = "m/dd/yyyy"

 

[Kdub via OfficeKB.com]

Thanks for you help, but that gave me the same result. Here
is what I found that works:

Rows(1).Insert
Range("A1").Value = "H"
Range("B1").Value = SheetName
Range("C1").NumberFormat = "@"
Range("C1") = Format(Date, ("MM/DD/YYYY"))

If I format the cell as text before storing the formatted date,
it stores my date in the correct format.
Thanks for the help.

Kevin

 

[Tom Ogilvy]

It may store it to appear as the correct format, but it isn't being stored
as a date value - at least not running your code in xl2003, US English.