Force "F2"+"Enter" on range of cells
excel, vba
Solution
I use this simple macro to apply F2 + Enter on the currently selected range:
Sub ApplyF2()
Selection.Value = Selection.FormulaR1C1
End Sub
Problem
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 ```