Using VBA to change a cell format to date doesn't get "committed"

[nick]

Hi,

I have some cells with the following formatted dates loaded from XML, they
come in as text formatted "01AUG2008"

I used the following VBA to change the cell storage format -> date
Selection.NumberFormatLocal = "[$-409]dd-mmm-yy;@"

Then I have a sumproduct formula that uses date ranges as one of it's
parameters. It doesn't pick up the cell data until I click in it and hit
enter once. Then it can be referenced. I took a macro of that action
"clicking and hit enter" but it looks to be setting a formula for each cell
specific to it's contents
ActiveCell.FormulaR1C1 = "18-Apr-2008"

What I want is the cell to be formatted as a date and without having to
click into it. Does anyone know how I can do this?

Thanks in advance,

Nick

 

[Gary''s Student]

Select the cells and run:

Sub ClickMe()
For Each r In Selection
r.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

This basically re-asserts the cells
THEN
apply the formatting.

 

[Dave Peterson]

If you have a bunch of data in a single column, you could

select the column
data|text to columns
Fixed width (don't add any lines and remove any excel added)
choose date dmy
and finish up the wizard

And format the cells the way you want.

You could record a macro when you do it manually if you needed code.

Hi,

I have some cells with the following formatted dates loaded from XML, they
come in as text formatted "01AUG2008"

I used the following VBA to change the cell storage format -> date
Selection.NumberFormatLocal = "[$-409]dd-mmm-yy;@"

Then I have a sumproduct formula that uses date ranges as one of it's
parameters. It doesn't pick up the cell data until I click in it and hit
enter once. Then it can be referenced. I took a macro of that action
"clicking and hit enter" but it looks to be setting a formula for each cell
specific to it's contents
ActiveCell.FormulaR1C1 = "18-Apr-2008"

What I want is the cell to be formatted as a date and without having to
click into it. Does anyone know how I can do this?

Thanks in advance,

Nick

 

[nick]

Thanks, that does the trick. Is there a way to do it without the loop? It's a
bit slow when there is a lot of data....

Select the cells and run:

Sub ClickMe()
For Each r In Selection
r.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

This basically re-asserts the cells
THEN
apply the formatting.
--
Gary''s Student - gsnu200841

Hi,

I have some cells with the following formatted dates loaded from XML, they
come in as text formatted "01AUG2008"

I used the following VBA to change the cell storage format -> date
Selection.NumberFormatLocal = "[$-409]dd-mmm-yy;@"

Then I have a sumproduct formula that uses date ranges as one of it's
parameters. It doesn't pick up the cell data until I click in it and hit
enter once. Then it can be referenced. I took a macro of that action
"clicking and hit enter" but it looks to be setting a formula for each cell
specific to it's contents
ActiveCell.FormulaR1C1 = "18-Apr-2008"

What I want is the cell to be formatted as a date and without having to
click into it. Does anyone know how I can do this?

Thanks in advance,

Nick