Changing the value of a year in a given format
 

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.

Changing the value of a year in a given format
 

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)

"cdbiggs" wrote in message
...
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.

Changing the value of a year in a given format
 

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

Not sure what that means.


"Bob Phillips" wrote:

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)

"cdbiggs" wrote in message
...
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.

Changing the value of a year in a given format
 

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)

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

Not sure what that means.


"Bob Phillips" wrote:

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)

"cdbiggs" wrote in message
...
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.

Changing the value of a year in a given format
 

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.

"Bob Phillips" wrote:

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)

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

Not sure what that means.


"Bob Phillips" wrote:

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)

"cdbiggs" wrote in message
...
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.