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.
--
JM