Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing year in a data cell | Excel Discussion (Misc queries) | |||
convert day of year to numeric value format year+day in 4 digits | Excel Worksheet Functions | |||
Automatically changing Year | Excel Discussion (Misc queries) | |||
changing the year of a date | Excel Worksheet Functions | |||
Changing the year only in a date | Excel Programming |