Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
"Rob" wrote in message ... 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 -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
current year
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" wrote in message ... 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current year and current week number | Excel Discussion (Misc queries) | |||
current week, current month, current year | Excel Programming | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
Get Current Year | Excel Programming | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel |