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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>