Force "F2"+"Enter" on range of cells

Question

I have an Excel 2010 worksheet which has macros to copy data from other sheets into a specific format on another sheet.

The data copies but I have an issue with the formatting of cell ranges which hold date or time values.

The data originates from a database extract and everything is in text format. In my worksheet when I copy the date (via VBA) I apply the format "yyyy-mm-dd" for dates and "hh:mm.ss.ss" for times.

There is never a fixed amount of rows so I've set the VBA code to apply the formatting to the range of cells for example:

AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row

shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"

Not all cells in the range have the correct format, they will appear as 15/04/2014 not 2014-04-15. If I manually select the cell and press the F2 then ENTER keys the format appears as I need. This happens randomly through the range and there could be thousands of rows so it is not practical to trawl though the worksheet manually hitting F2+ENTER on each one.

I've looked on the internet and found what should automatically do the F2+ENTER with VBA.

The code below is extracted from a larger set of lines of code, so the Dim statements etc. are further up in the actual copy, but this should show the way I've tackled this so far.

Dim shAss As Worksheet
Dim AssDateLastRow As Long
Dim c As Range

'enter method to format 'Date Craftperson Assigned' and 'Time Craftperson Assigned' in   Assignments sheet
'column "C" and "D", to formats required by Archibus: date "yyyy-mm-dd", time  "hh:mm.ss.ss"
AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row
shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"
'ensure format is applied by forcing F2 edit of cell
For Each c In shAss.Range("C4:C" & AssDateLastRow).Cells
    c.Select
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
'Selection.NumberFormat = "yyyy-mm-dd"
Next

When I run the code, the data copies into my worksheets but the dates and times are still in a mixed format.

The attempt at forcing the F2+ENTER via the VBA doesn't seemed to have done anything. If done manually it works okay.

Below is an example of data copied from the results in the worksheet

Work Request Code       Date Assigned       Time  Assigned
92926                   19/05/2014          14:30.00.00
92927                   19/05/2014          15:00.00.00
92928                   2014-05-19          15:15.00.00
92934                   2014-05-19          14:00.00.00
92527                   12/05/2014          07:30
92528                   12/05/2014          08:00
92804                   2014-05-12          16:15
92805                   2014-05-12          16:20.00.00

Answer 1

I use this simple macro to apply F2 + Enter on the currently selected range:

Sub ApplyF2()
    Selection.Value = Selection.FormulaR1C1
End Sub