title: Force "F2"+"Enter" on range of cells
body: <p>I have an Excel 2010 worksheet which has macros to copy data from other sheets into a specific format on another sheet.</p>
  
  <p>The data copies but I have an issue with the formatting of cell ranges which hold date or time values.</p>
  
  <p>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 <code>"yyyy-mm-dd"</code> for dates and <code>"hh:mm.ss.ss"</code> for times.</p>
  
  <p>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:</p>
  
  <pre><code>AssDateLastRow = shAss.Range("C" &amp; Rows.Count).End(xlUp).Row
  
  shAss.Range("C4:C" &amp; AssDateLastRow).NumberFormat = "yyyy-mm-dd"
  </code></pre>
  
  <p>Not all cells in the range have the correct format, they will appear as <code>15/04/2014</code> not <code>2014-04-15</code>.  If I manually select the cell and press the <kbd>F2</kbd> then <kbd>ENTER</kbd> 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 <kbd>F2</kbd>+<kbd>ENTER</kbd> on each one.</p>
  
  <p>I've looked on the internet and found what should automatically do the <kbd>F2</kbd>+<kbd>ENTER</kbd> with VBA.</p>
  
  <p>The code below is extracted from a larger set of lines of code, so the <code>Dim</code> statements etc. are further up in the actual copy, but this should show the way I've tackled this so far.</p>
  
  <pre><code>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" &amp; Rows.Count).End(xlUp).Row
  shAss.Range("C4:C" &amp; AssDateLastRow).NumberFormat = "yyyy-mm-dd"
  'ensure format is applied by forcing F2 edit of cell
  For Each c In shAss.Range("C4:C" &amp; AssDateLastRow).Cells
      c.Select
      SendKeys "{F2}", True
      SendKeys "{ENTER}", True
  'Selection.NumberFormat = "yyyy-mm-dd"
  Next
  </code></pre>
  
  <p>When I run the code, the data copies into my worksheets but the dates and times are still in a mixed format.</p>
  
  <p>The attempt at forcing the <kbd>F2</kbd>+<kbd>ENTER</kbd> via the VBA doesn't  seemed to have done anything.  If done manually it works okay.</p>
  
  <p>Below is an example of data copied from the results in the worksheet</p>
  
  <pre><code>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
  </code></pre>