Date problem using VBA

[Guest]

I have written a small macro to format raw data from an accounting system to
a useable format in excel. The accounting system stores dates as dd.mm.yyyy.
As Excel wont recognise this as a date, the code I have written to convert
the date columns is simply:
Columns("E:E").Replace What:=".", Replacement:="/"
Columns("E:E").NumberFormat = "dd/mm/yyyy;@"
However, any date that wouldn't make sense in an american format
(mm/dd/yyyy) stays stored as text until you click in the actual cell. All my
settings are up as English UK, why is this happening? I have even tried
adding tricks into the VBA code that work to convert these date fields
manually, but don't in the VBA code! An example is typing "1" into an empty
cell, then copying, and paste special multiply across all the date range.
Works manually, but not in VBA!
The line where the error starts is the 'replacement' line. If I do this
manually excel immediately recognises the entire column as dates. If I do
this with VBA code it doesn't. Any ideas?
Thanks.

 

[HiArt]

Hi

Columns("E:E").NumberFormat = "dd/mm/yyyy;@"

not sure you need the ;@ at the end of the NumberFormat argument. You
could try that.

Otherwise this code works fine for me. Have you turned off automatic
updates, secrren refreshes or something?

Art

 

[Guest]

HiArt,

Thanks for your response. The problem I am certain lies in some setting
where VBA is thinking dates in US format, and the spreadsheet settings are in
UK format, but I can't for the life of me figure it out. My logic for
thinking this is that, after running the macro, if I go into the spreadsheet
, turn on the macro recorder, and double click on one of these cells that
aren't recognised as date (e.g. cell E7 containing "30/06/2005") and hit
return (so now it magically does recognise as a date) and then read the code
the macro recorder has written, it shows:
ActiveCell.FormulaR1C1 = "6/30/2004"
Range("E8").Select
Anyway, I have found the following slightly unsatisfactory temporary fix. I
have adjusted the code as follows:
Columns("E:E").Replace What:=".", Replacement:="/"
Range("E7").Select
Do Until ActiveCell.Value = ""
ActiveCell.FormulaR1C1 = DateValue(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
Columns("E:E").NumberFormat = "dd/mm/yyyy"

Works for now. Let me know if you have any other brainwaves.

JM

 

[Koen_VN]

Hi,

I'm not quite sure, but try to verify your settings (start - contro
panel - regional and language options). Check what's in 'short date'
maybe by changing this, your problem will be solved.

Koe