+ Reply to Thread
Results 1 to 5 of 5

Changing the value of a year in a given format

  1. #1
    cdbiggs
    Guest

    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.

  2. #2
    Bob Phillips
    Guest

    Re: 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" <cdbiggs@discussions.microsoft.com> wrote in message
    news:72E4FCD9-15EB-4804-80E8-181C9C2D4432@microsoft.com...
    > 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.




  3. #3
    cdbiggs
    Guest

    Re: 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" <cdbiggs@discussions.microsoft.com> wrote in message
    > news:72E4FCD9-15EB-4804-80E8-181C9C2D4432@microsoft.com...
    > > 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.

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: 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" <cdbiggs@discussions.microsoft.com> wrote in message
    news:94F94099-A956-4A5E-AC33-FE66D31FFDEA@microsoft.com...
    > 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" <cdbiggs@discussions.microsoft.com> wrote in message
    > > news:72E4FCD9-15EB-4804-80E8-181C9C2D4432@microsoft.com...
    > > > 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.

    > >
    > >
    > >




  5. #5
    cdbiggs
    Guest

    Re: 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" <cdbiggs@discussions.microsoft.com> wrote in message
    > news:94F94099-A956-4A5E-AC33-FE66D31FFDEA@microsoft.com...
    > > 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" <cdbiggs@discussions.microsoft.com> wrote in message
    > > > news:72E4FCD9-15EB-4804-80E8-181C9C2D4432@microsoft.com...
    > > > > 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.
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1