Trying to create macro to perform the "F2 + Enter"

[muzikman69]

I am trying to create a macro which will perform the F2 + Enter keystroke, to update formatting for cells.

My difficulty is coming in that the macro recorded below had the "1/2/2007" hard coded into it, and when the date changes on the sheet it continues by updating the cells to the above mentioned date. Is there an easy way to have the formating updated for many lines?

Thanks for any help,
Graeme


Sub F2Enter()
'
' F2Enter Macro
' Macro recorded 7/31/2007 by grander
'
' Keyboard Shortcut: Ctrl+f
'
ActiveCell.FormulaR1C1 = "1/2/2007"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

 

[Norie]

What is it you actually want to achieve?

 

[muzikman69]

Here is what I have


Cell A1 = 2/1/2007


I go into the cell formating, and change the format to show "Feb-07". However in order to have this new formatting appear, I seem to have to do the F2+Enter keystroke.

After keystroke cell should equal

Cell A1 = "Feb-07"


Make sense?

Graeme,

 

[Jonmo1]

Sounds like you have "Numbers Stored As Text"..

Try typing a 1 anywhere on your sheet, copy it.
Highlight offending range, paste special - values - multiply

 

[muzikman69]

Forgot one thing,,, the reason F2+Enter is required is because the date comes from a concatenate formula, then is copy and pasted as a value.... so it is formatted as text and not a date until the F2+Enter keystroke is entered...

Cheers,
Graeme,

 

[Jonmo1]

adjust that formula to return a number, not text....

like say your current formula is

=concatenate(a1,b1)

change it to

=--concatenate(a1,b1)

 

[Scott Huish]

Why are you using CONCATENATE to create a date? Why not use a function that actually returns a date such as DATE?

 

[muzikman69]

Stupid me.... I knew that.

It worked like a charm,,, thanks for the help....