Replace Incorrect Date In Cell To Another Valid Date

[Rick Rothstein]

I want replace the 1/1/9999 to normal date.

Scenario:

On E column : I have 18/03/13(like this dates)
On L column : I have 1/1/9999(like this dates)

Now I want to replace the cell value in L column which is in present in E column. First it has to search for this format 1/1/9999 in given column and has to replace all these cells by 18/03/13 dates.

I have assumed you have several different dates in Column E and you want to move each one over to Column L only if the entry in Column L is 1/1/9999. If that assumption is correct, then I think this macro will do that for you...

Sub Replace9999Dates()
  Dim LR As Long
  LR = Cells(Rows.Count, "L").End(xlUp).Row
  Range("L1:L" & LR) = Evaluate("IF(L1:L" & LR & "=0+""1/1/9999"",E1:E" & LR & _
                                ",IF(LEN(L1:L" & LR & "),L1:L" & LR & ",""""))")
End Sub