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

My variation

n = Selection.Rows.count
Dim r1 As range, rv As range
Set r1 = Selection.Cells(1, 1)
For I = 1 To n
Set rv = r1.offset(I - 1, 0)
vali = rv.value
 IsNumeric(vali) Then
 vali = CDbl(vali)
 rv.value = 0
 rv.value = vali
 End If

Answer 2

Sub RefreshCells()

Dim r As Range, rr As Range
Set rr = Selection
For Each r In rr
r.Select
Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False

    Application.SendKeys "{F2}"
    Application.SendKeys "{ENTER}"
    Application.SendKeys "+{ENTER}"
    DoEvents
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False

    Application.SendKeys "{ENTER}"

    DoEvents

Next



End Sub

Answer 3

I can think of two options to get Excel to apply the formatting to the cells in one step.

The first is to use the Text to columns functionality even though there is nothing in the column to split. The second option is to copy a value of 1 and paste it into the cells using the Paste Special - Multiply option.

Although either method should force an update of the cell formating, I would lean towards the first option. This is incase some of your dates are is stored as text.

    Sub Format_Text_to_Columns()

    Dim AssDateLastRow As Long

    AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
    'Set the format

        Range("C4:C" & AssDateLastRow).Select
        Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
         Space:=True, FieldInfo:=Array(1, 5)
    'Use text to columns to force a format update

    End Sub



    Sub Format_Paste_Special_Multiply()

    Dim AssDateLastRow As Long

    AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
    'Set the format

        Range("C1").FormulaR1C1 = "1"
        Range("C1").Copy
        Range("C4:C" & AssDateLastRow).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
        Application.CutCopyMode = False
        Range("C1").ClearContents
    'Multiply the dates by 1 to force a format update

    End Sub

Answer 4

I struggled to get this to work too. My problem has been not just dates but also data with a single quote in front of it. What I hacked together works great for me. It cleans up over 70,000 cells very fast. Hope it works for you:

(you will change the range and such to suit your needs)

    Dim MyRange As Range

    Set MyRange = Range(Cells(2, 7), [G1].End(xlDown))

    For Each MyRange In MyRange.Cells
    'Mimic F2 without SendKeys
        MyRange.Value = MyRange.Value
    Next

Answer 5

It seems odd that you would need to send keys F2 + Enter. What is the formatting before you run the macro? Try formatting the whole column that way (it won't affect the text).

Columns("C:C").NumberFormat = "yyyy-mm-dd"

Answer 6

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

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