Changing the value of a year in a given format

[Guest]

I'm trying to write a sub that will take a date from a given cell - say
1/01/2006, and then change that to read 1/01/2004. I would physically go in
to do it to each cell, however, there' s over 10,000 cells, and I thought a
program would be easier. Any assistance in this matter would be greatly
appreciated. Thanks.

 

[Bob Phillips]

Untested

For Each cell In Selection
With cell
If IsDate(.VAlue) Then
If Year(.Value) = 2006 Then
.Value =
DateSerial(Year(.Value)-2,Month(.value),Day(.Value))
End If
End If
End With
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I typed it in verbatim, and I get an error box that only says "400"...???

Not sure what that means.

 

[Bob Phillips]

Try this

For Each cell In Selection
With cell
If IsDate(.Value) Then
If Year(.Value) = 2006 Then
.Value = DateSerial(Year(.Value) - 2, _
Month(.Value), Day(.Value))
End If
End If
End With
Next cell


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,

No...this didn't work. For some reason, it's not even looping through. It
wouldn't even change the first one on the worksheet, which is in cell A1.
All the dates are in the "A" column.