I would like to change to year portion from 2009 to 2010 in multiple cells.
The cells are formatted as Date with *3/14/2001 selected

for k = 1 to 5
Sheets("Sheet1").Cells(k,2)=Date
if Date.year = 2009 then Date.year=2010
next k

Date.year is incorrect. How do I change and save the date?

Sub dural()
Dim d As Date, s As String
For i = 1 To 30
d = Cells(i, 1).Value
m = Month(d)
dt = Day(d)
s = m & "/" & dt & "/" & "2010"
Cells(i, 1).Value = DateValue(s)
Next
End Sub

--
Gary''s Student - gsnu200909


"Philosophaie" wrote:

> I would like to change to year portion from 2009 to 2010 in multiple cells.
> The cells are formatted as Date with *3/14/2001 selected
>
> for k = 1 to 5
> Sheets("Sheet1").Cells(k,2)=Date
> if Date.year = 2009 then Date.year=2010
> next k
>
> Date.year is incorrect. How do I change and save the date?

If you're really populating those cells with the date a year from today...

Dim NewDate as date
newdate = dateserial(year(date)+1,month(date),day(date))
with worksheets("sheet1").cells(1,2).resize(5,1)
.numberformat = "mm/dd/yyyy"
.value = newdate
end with

It'll work for most dates--not sure what you want to happen if you run this on
Feb 29 of a leap year.



Philosophaie wrote:
>
> I would like to change to year portion from 2009 to 2010 in multiple cells.
> The cells are formatted as Date with *3/14/2001 selected
>
> for k = 1 to 5
> Sheets("Sheet1").Cells(k,2)=Date
> if Date.year = 2009 then Date.year=2010
> next k
>
> Date.year is incorrect. How do I change and save the date?

--

Dave Peterson

It is a little unclear from your code exactly what you are trying to do
(Date is a built in function in VB), but assuming you have a real date
reference of some kind, just put it in where I show ThisYearsDate and the
DateAdd function (as structured) will add one year to that date. I show the
output being assigned to a variable named NextYearsDate, but you can output
it anywhere you need to (back into a cell, in a MsgBox, etc.)...

NextYearsDate = DateAdd("YYYY", 1, ThisYearsDate)

--
Rick (MVP - Excel)


"Philosophaie" <(E-Mail Removed)> wrote in message
news:11024AC7-5D6A-4AC5-B46D-(E-Mail Removed)...
>I would like to change to year portion from 2009 to 2010 in multiple cells.
> The cells are formatted as Date with *3/14/2001 selected
>
> for k = 1 to 5
> Sheets("Sheet1").Cells(k,2)=Date
> if Date.year = 2009 then Date.year=2010
> next k
>
> Date.year is incorrect. How do I change and save the date?

I am wanting to read the year from a cell who is formatted as a date and has
a date in it. If the year is not 2010 then I want to change it to 2010.

"Rick Rothstein" wrote:

> It is a little unclear from your code exactly what you are trying to do
> (Date is a built in function in VB), but assuming you have a real date
> reference of some kind, just put it in where I show ThisYearsDate and the
> DateAdd function (as structured) will add one year to that date. I show the
> output being assigned to a variable named NextYearsDate, but you can output
> it anywhere you need to (back into a cell, in a MsgBox, etc.)...
>
> NextYearsDate = DateAdd("YYYY", 1, ThisYearsDate)
>
> --
> Rick (MVP - Excel)
>
>
> "Philosophaie" <(E-Mail Removed)> wrote in message
> news:11024AC7-5D6A-4AC5-B46D-(E-Mail Removed)...
> >I would like to change to year portion from 2009 to 2010 in multiple cells.
> > The cells are formatted as Date with *3/14/2001 selected
> >
> > for k = 1 to 5
> > Sheets("Sheet1").Cells(k,2)=Date
> > if Date.year = 2009 then Date.year=2010
> > next k
> >
> > Date.year is incorrect. How do I change and save the date?
>
> .
>

Try it this way...

With Sheets("Sheet1")
For K = 1 To 5
With .Cells(K, 2)
.Value = DateSerial(2010, Month(.Value), Day(.Value))
.NumberFormat = "m/d/yyyy"
End With
Next
End With

--
Rick (MVP - Excel)


"Philosophaie" <(E-Mail Removed)> wrote in message
news:11024AC7-5D6A-4AC5-B46D-(E-Mail Removed)...
>I would like to change to year portion from 2009 to 2010 in multiple cells.
> The cells are formatted as Date with *3/14/2001 selected
>
> for k = 1 to 5
> Sheets("Sheet1").Cells(k,2)=Date
> if Date.year = 2009 then Date.year=2010
> next k
>
> Date.year is incorrect. How do I change and save the date?