DoubleClick mystery

[donwb]

Win XP Excel 2003

I have exported an Outlook calendar to an Excel file.
In the "StartDate" column of the resulting file,
I would like all dates to have the same format of dd/mm/yyyy.
Some do and if I click Format/Cells, the 'dates' format is confirmed.

However some dates seem to have an indeterminate format.
For example, some appear as 2/2/2007 or 12/3/2008.
If I click Format/Cells, the format says General.
If I then select Dates and the format I require, there is no change to the
cell.

If I manually double click the date cell or press function key F2,
and then select another cell,
this results in the cell showing the date in the form dd/mm/yyyy, as I
require.

I have tried to automate this DoubleClick procedure in VBA:-

Sub Macro1()
For Each c In Range("B1:B2777 ")
c.Select
Application.DoubleClick
Next c
End Sub

But this does'nt work.
Neither does Application.SendKeys("F2")

What am I missing here?
donwb

 

[OssieMac]

Make sure that you backup your workbook first in case of undesired results.

Not sure if this will work for you but try inserting 1 in any blank cell.
Copy the cell then select all of the cells with the dates and then Past
Special -> Multiply. Basically it multiplies all the dates by 1 which should
return the same value.

The above works if you have cells formatted as text and convert the
formatting to numeric so perhaps it will work for your situaltion also.

 

[donwb]

Hi OssieMac, it worked.
Many thanks
donwb

Make sure that you backup your workbook first in case of undesired
results.

Not sure if this will work for you but try inserting 1 in any blank cell.
Copy the cell then select all of the cells with the dates and then Past
Special -> Multiply. Basically it multiplies all the dates by 1 which
should
return the same value.

The above works if you have cells formatted as text and convert the
formatting to numeric so perhaps it will work for your situaltion also.