current year

[Rob]

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.

 

[Jacob Skaria]

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

 

[Rob]

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).

 

[OssieMac]

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

 

[Jeff]

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

 

[Rick Rothstein]

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.

 

[Sammy]

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

--

 

[Rob]

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