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

Sub DateTesterBaselineIncrease()
Dim Baseline As Date
Dim Rng As Range
Baseline = "1/1/2005"
Set Rng = ThisWorkbook.Worksheets("Settings_North").Range("B 2")
ThisWorkbook.Activate
rng.Parent.Select
Rng.Select

Do Until Selection = ""
SelCol = Selection - Baseline
With Selection
.Value = DateSerial(year(.Value) + 1, Month(.Value), Day(.Value))
End With
Selection.Offset(1, 0).Select
loop

End Sub

What is the point of the SelCol calculation. You don't use it.



--
Regards,
Tom Ogilvy


"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

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

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

Yes, that code will error, because you just have ".Value", not in a With
block.
What I meant with that line was, how do you need to use the 2 worksheets
that mentioned in that line ?
Where are the values coming from/going to ?
And I still see no purpose for the Baseline.

NickHK

"jfcby" wrote in message
ps.com...
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

Hello NickHK,

I'm working on a calendar project. In sheet6 is my calendar that I put
a spinner button on to increase the year. Sheet6 Column2 Row2 is where
my dates are formated like so 1/1/2006 that will change when the
spinner button is clicked in sheet2.

My dates a
1/1/2006
1/15/2006
4/6/2006
7/8/2006
12/25/2006

I hope this is the information you wanted if not let me know and I'll
be happy to give more details.

Thank you for your help,
jfcby


NickHK wrote:
Yes, that code will error, because you just have ".Value", not in a With
block.
What I meant with that line was, how do you need to use the 2 worksheets
that mentioned in that line ?
Where are the values coming from/going to ?
And I still see no purpose for the Baseline.

NickHK

"jfcby" wrote in message
ps.com...
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

Adjust names/ranges to suit :

Dim DestWS As Worksheet
Dim SourceWs As Worksheet

Set DestWS = Worksheets("Sheet6")
Set SourceWs = Worksheets("Sheet2")

With SourceWs.Range("B2")
DestWS.Range("B2").Value = DateSerial(Year(.Value) - 1, Month(.Value),
Day(.Value))
End With

NickHK

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

I'm working on a calendar project. In sheet6 is my calendar that I put
a spinner button on to increase the year. Sheet6 Column2 Row2 is where
my dates are formated like so 1/1/2006 that will change when the
spinner button is clicked in sheet2.

My dates a
1/1/2006
1/15/2006
4/6/2006
7/8/2006
12/25/2006

I hope this is the information you wanted if not let me know and I'll
be happy to give more details.

Thank you for your help,
jfcby


NickHK wrote:
Yes, that code will error, because you just have ".Value", not in a With
block.
What I meant with that line was, how do you need to use the 2 worksheets
that mentioned in that line ?
Where are the values coming from/going to ?
And I still see no purpose for the Baseline.

NickHK

"jfcby" wrote in message
ps.com...
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