title: Using a macro + IF formula + static date?
body: <p>I've only just started using Excel so am a bit of a newbie - please excuse my ignorance/lack of proper terminology!</p>
  
  <p>I currently have column E, where people will manually enter a 'Y' to flag certain accounts. Once these accounts have been flagged, I have a macro button to change all these Y's to 'Flagged', so that I know they've been dealt with. </p>
  
  <p>I wanted column F to add in the date that these were flagged, so I have an IF formula to say that if 'Raised' is in colE, add in today's date in colF. I was having issues with 2 things, firstly I need it to ignore any that already have a date in colF (as I don't want old dates overridden), and I didn't know how to 'freeze' the date. Currently, any time I open the worksheet it changes all the old dates to todays date, but I need to keep whatever the date was at the time it was entered. </p>
  
  <p>All of my excel is self taught through reading forums (so please forgive me if this is terrible!), but at the moment I've come up with this:</p>
  
  <pre><code>Sub ResetFlags()
  '
  ' ResetFlags Macro
  '
  
  '
  Sheets("Matrix").Select
  Columns("E:E").Select
  Range("E2").Activate
  Selection.Replace What:="y", Replacement:="Flagged", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  Range("E12").Select
  
  Dim r As Range
  Set r = Range("F:F").Cells.SpecialCells(xlCellTypeBlanks)
  On Error Resume Next
  r.Formula = "=IF((RC[-1]=""Flagged""),(TODAY()),"""")"
  On Error Resume Next
  
  End Sub
  </code></pre>
  
  <p>The macro seems to work well in that it replaces the Y's with 'Flagged', it skips old dates and add today's date.. BUT every time I open the workbook it changes ALL the dates to today. How to I freeze the date?!</p>
  
  <p>Also, when I run the macro, although it all runs through at the very end I get an error box saying 'Run-time error 1004: No cells were found'. I can't figure this out/how to get rid of it. </p>
  
  <p>Thank you very much!!</p>