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

--
JM


"HiArt" wrote:


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


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=390125