Excel VBA Question

[coox]

Hi, format type thing really, see if you can understand me...

I've got some dates in cells (hundreds of them), and excel currently recognises them as text, eg. "12/02/99", not dates. However, if I select each one and press F2 followed by enter, excel then converts them to proper date formated cells, and they then appear in the formula bar in the format "12/02/1999". Now, I can spend a week or two press F2 on each and every bloody one of them, or someone could tell me how to automate this. I don't really understand why I need to do the F2 thing...

[Shark]

Select your column and on the toolbar select "Format-Cells" and change the format type to Date.

[JHausmann]

Or, if they're not all in the same column, record a macro and run as needed...

[coox]

Sorry guys, I fear you rather missed the point. Formatting them as Shark suggested made no difference until I did the F2/Return thing on EVERY one of them. Recording a macro to do this simply resulted in code that explicitly put the contents of the cell, eg:

ActiveCell.FormulaR1C1 = "21/12/99"

Which obviously wasn't going to be any good either. I did, however, discover a solution:

Do While Not Trim(Selection) = ""
 Selection = CDate(Selection)
 ActiveCell.Range("A2").Select
Loop

Thanks for your brainwaves, though...

[Nitro]

I think you can write a DO LOOP with

activecell.value = activell.value

will do the trick too.



Another alternative is to copy the column to another column that has been formatted with date already.