Date + one year
When I put in the name of the workbook I get an error saying "Expected: End
of statement" with Manor highlighted
Set WB = RIDGE MANOR COMMUNITY UNITED METHODIST CHURCH MEMBERSHIP LISTV1.XLS
'<<===== CHANGE
oldjay
"Norman Jones" wrote:
Hi Oldjay,
Try:
'=============
Sub AddOneYear()
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Const col As String = "D" '<<===== CHANGE
Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Birthday") '<<===== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
LRow = SH.Cells(Rows.Count, col).End(xlUp)
Set rng = SH.Range(col & 2).Resize(LRow - 1)
For Each rcell In rng.Cells
With rcell
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value) + 1, _
Month(.Value), Day(.Value))
End If
End With
Next rcell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============
Change the sheet and workbook names to suit.
As written, the macro will add a year to all dates in column D and allows
for headers in row 1.
--
---
Regards,
Norman
"Oldjay" wrote in message
...
A little more detail. The birthdays are in the 4th column of a 8 column
list.
I would like the macro to change them in place
oldjay
"RB Smissaert" wrote:
This will do it in VBA:
Function AddOneYear(daDate As Date) As Date
AddOneYear = DateSerial(Year(daDate) + 1, Month(daDate), Day(daDate))
End Function
Sub test()
Dim i As Long
For i = 1 To 100
Cells(i, 2) = Format(AddOneYear(Cells(i, 1)), "dd/mm/yy")
Next
End Sub
RBS
"Oldjay" wrote in message
...
I have a long column of birthdays formulated 8/12/05
I want to change all the dates to next year i.e. 8/12/06
oldjay
|