I wan to create a macro which will change the dates once a year to the
current year. I have a range of dates beginning with 01/01/2008 and I want
to change these dates to the current year and I want to be able to run this
macro each year to update the year that is in the cells to the current year.
I can replace code but how do I state that to replace 200* with the current
year. Please help...Thanks in advance.

Dear Rob

Find and Replace should be the easiest way to do this. Would like to know
whether all entries are displayed if you enter year to be replaced and click
'Find All' in the Find and Replace Window

If this post helps click Yes
--------------
Jacob Skaria

"Rob" wrote:

> I wan to create a macro which will change the dates once a year to the
> current year. I have a range of dates beginning with 01/01/2008 and I want
> to change these dates to the current year and I want to be able to run this
> macro each year to update the year that is in the cells to the current year.
> I can replace code but how do I state that to replace 200* with the current
> year. Please help...Thanks in advance.

Here is what I have written thus far:

Selection.Replace What:="200", Replacement:="2009", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I want to replace the "What" portion with "200" and a wild card like (*) to
capture all years and the Replacement clause with the current year. (Not
just 2009 but any current year).

Hi Rob,

You refer to a range of dates. As 2008 was a leap year, is Feb 29 one of the
dates? If so, how do you want to handle it?

Is the range consecutive like all dates from jan 1 to dec 31? If so, the the
method of entry can be used. Assume first cell is A2 and contains 1/1/2008.
Next cell contains the formula =A2+1 and you copy that formula down. Then
for the next year just edit cell A2 to reflect year 2009.

Regards,

OssieMac


"Rob" <(E-Mail Removed)> wrote in message
news:FDC59078-D0A5-4750-8B51-(E-Mail Removed)...
>I wan to create a macro which will change the dates once a year to the
> current year. I have a range of dates beginning with 01/01/2008 and I
> want
> to change these dates to the current year and I want to be able to run
> this
> macro each year to update the year that is in the cells to the current
> year.
> I can replace code but how do I state that to replace 200* with the
> current
> year. Please help...Thanks in advance.

Myabe Somthing like This is what you're needing

Option Explicit
Dim DataArray() As Variant
Sub CurrentYear()
Dim Worksheet As Worksheet
Dim aLastRow As Long
Dim i As Variant

Set Worksheet = Worksheets("Sheet1") '<--Change to match your Sheet
aLastRow = Worksheet.Cells(Rows.Count, 1).End(xlUp).Row
ReDim DataArray(0 To aLastRow)

On Error Resume Next
For i = 0 To aLastRow
DataArray(i) = Worksheet.Cells(i + 1, 1)
Worksheet.Cells(i + 1, 1) = Left(DataArray(i), _
Len(DataArray(i)) - 4) & Year(Now)
Next
End Sub


"OssieMac" wrote:

> Hi Rob,
>
> You refer to a range of dates. As 2008 was a leap year, is Feb 29 one of the
> dates? If so, how do you want to handle it?
>
> Is the range consecutive like all dates from jan 1 to dec 31? If so, the the
> method of entry can be used. Assume first cell is A2 and contains 1/1/2008.
> Next cell contains the formula =A2+1 and you copy that formula down. Then
> for the next year just edit cell A2 to reflect year 2009.
>
> Regards,
>
> OssieMac
>
>
> "Rob" <(E-Mail Removed)> wrote in message
> news:FDC59078-D0A5-4750-8B51-(E-Mail Removed)...
> >I wan to create a macro which will change the dates once a year to the
> > current year. I have a range of dates beginning with 01/01/2008 and I
> > want
> > to change these dates to the current year and I want to be able to run
> > this
> > macro each year to update the year that is in the cells to the current
> > year.
> > I can replace code but how do I state that to replace 200* with the
> > current
> > year. Please help...Thanks in advance.
>
>

Maybe this macro will do what you want...

Sub AddOneYearToSelection()
Dim R As Range
For Each R In Worksheets("Sheet2").Range("A2:A367")
If IsDate(R.Value) Then R.Value = DateAdd("yyyy", 1, R.Value)
Next
End Sub

Just change the worksheet and cell references (in the For..Each statement)
to whatever your actual conditions are.

--
Rick (MVP - Excel)


"Rob" <(E-Mail Removed)> wrote in message
news:FDC59078-D0A5-4750-8B51-(E-Mail Removed)...
>I wan to create a macro which will change the dates once a year to the
> current year. I have a range of dates beginning with 01/01/2008 and I
> want
> to change these dates to the current year and I want to be able to run
> this
> macro each year to update the year that is in the cells to the current
> year.
> I can replace code but how do I state that to replace 200* with the
> current
> year. Please help...Thanks in advance.

"Rob" <(E-Mail Removed)> wrote in message
news:FDC59078-D0A5-4750-8B51-(E-Mail Removed)...
>I wan to create a macro which will change the dates once a year to the
> current year. I have a range of dates beginning with 01/01/2008 and I
> want
> to change these dates to the current year and I want to be able to run
> this
> macro each year to update the year that is in the cells to the current
> year.
> I can replace code but how do I state that to replace 200* with the
> current
> year. Please help...Thanks in advance.

Hi Rob

The variation I use, and find very useful, is below. Refer to the value of
either 'GetYear' or 'a'. This method using WorkBook_Activate() means that
EVERY time the Workbook opens the Year will be current, meaning you will
have no replacement to make.

-- Code

Private Sub Workbook_Activate()
Dim GetDate, GetYear

GetDate = Now
GetYear = Year(GetDate)
a = GetYear

[a1] = a '[##] = shorthand method of refering to cells
End Sub

--

This is exactly what I wanted OssieMac. Thanks so much!!
"OssieMac" wrote:

> Hi again Rob,
>
> Is this what you are looking for?
>
> Dim strYear As String
> strYear = Year(Date) 'Year of today's date
>
> Selection.Replace What:="200*", _
> Replacement:=strYear, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
>
> Regards,
>
> OssieMac
> "Rob" <(E-Mail Removed)> wrote in message
> news:CC24010A-528C-4726-9418-(E-Mail Removed)...
> > Here is what I have written thus far:
> >
> > Selection.Replace What:="200", Replacement:="2009", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> > ReplaceFormat:=False
> >
> > I want to replace the "What" portion with "200" and a wild card like (*)
> > to
> > capture all years and the Replacement clause with the current year. (Not
> > just 2009 but any current year).
>