Hello NickHK,

I tried your code and it gives me this error message:

Compile error:
Invalid or unqualified Reference

This is the two ways I tried the code and both of them give me the
above error message highlighting the .Value after the Year:

..Value = DateSerial(Year(.Value) - 1, Month(.Value), Day(.Value))

Sub DateTesterBaselineIncrease()
Dim SourceRng As Range
Dim BaseLine As Date
BaseLine = DateSerial(2005, 10, 1)
With ThisWorkbook.Worksheets("Settings")
Set SourceRng = .Range("B2", .Range("B2").End(xlDown))
End With
..Value = DateSerial(Year(.Value) + 1, Month(.Value), Day(.Value))
End Sub


Sub DateTesterBaselineDecrease()
Dim SourceRng As Range
With ThisWorkbook.Worksheets("Settings")
Set SourceRng = .Range("B2", .Range("B2").End(xlDown))
End With
.Value = DateSerial(Year(.Value) - 1, Month(.Value), Day(.Value))

End Sub

What could be causing the error messages?

Thank you for your help,
jfcby

NickHK wrote:
You can't .Select a range unless it is on the ActiveSheet.
So you would need a
ThisWorkbook.Worksheets("Settings_North").Activate first.

However, it seldom necessary or desiarable to .Select before using them.
Dim SourceRng As Range
With ThisWorkbook.Worksheets("Settings_North")
Set SourceRng = .Range("B2", .Range("B2").End(xlDown))
End With

The 2 worksheets are involved with this line ?
.Value = DateSerial(year(.Value) + 1, Month(.Value), Day(.Value))

Also it a good idea to be explicit about setting dates, otherwise you may
find yourself dependent on Local Settings as to what e.g. "1/10/2005" means.
Baseline = DateSerial(2005, 10, 1) 'although "1/1/2005" is unambiguous
although I do not see you using Baseline here.

NickHK

"jfcby" wrote in message
ups.com...
Hello,

Can this code be changed to reference 2 worksheets?

Sub DateTesterBaselineIncrease()
Dim Baseline As Date
Dim Rng As Range
Baseline = "1/1/2005"
Set Rng = ThisWorkbook.Worksheets("Settings_North").Range("B 2")
Rng.Select
Do
Do Until Selection = ""
SelCol = Selection - Baseline
With Selection
.Value = DateSerial(year(.Value) + 1, Month(.Value), Day(.Value))
End With
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""
End Sub

I get ths error message:

Run time error '1004'
Select method of range class failed

when debug it highlights this line:

Rng.Select

Thank you for your help,
jfcby