Norman,

The code works great. Thanks

I have another column with Anniversaries that I also want to convert. I
tried to just change the Const col As String = "J" to Const col As String =
"K" at the botton of the sub and re-copy the code below This doesn't work. It
gives the error"Duplicate declaration in current scope"

oldjay

"Norman Jones" wrote:


Hi Oldjay,

If the code is to be housed in the RIDGE MANOR COMMUNITY UNITED METHODIST
CHURCH MEMBERSHIP LISTV1.XLS workbook, you can leave the original:

Set WB = ThisWorkbook

Otherwise, try:

Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")


---
Regards,
Norman


"Oldjay" wrote in message
...
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