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("B2")
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("B2")
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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("B2")
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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("B2")
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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("B2")
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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("B2")
> > > 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 are:
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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("B2")
> > > > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 are:
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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("B2")
> > > > > 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,

Thank you for your help! The code you provided did not work the way I
needed so I found another code and modified it to work.

Modified Working Code:

Sub DateTesterBaselineIncrease()
Dim cell As Range
For Each cell In Worksheets(2).UsedRange.Columns(2).Cells
If cell >= DateSerial(1900, 1, 1) Then
With cell
.Value = DateSerial(Year(.Value) + 1, Month(.Value), Day(.Value))
End With
End If
Next
End Sub

Thank you for your help,
jfcby

NickHK wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 are:
> > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > 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("B2")
> > > > > > 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
> > > > > >
> > > >
> >

Glad you got it working, but I don't see where the 2 worksheets come in.
All the .values relate to the same cell, but...

NickHK

"jfcby" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello NickHK,
>
> Thank you for your help! The code you provided did not work the way I
> needed so I found another code and modified it to work.
>
> Modified Working Code:
>
> Sub DateTesterBaselineIncrease()
> Dim cell As Range
> For Each cell In Worksheets(2).UsedRange.Columns(2).Cells
> If cell >= DateSerial(1900, 1, 1) Then
> With cell
> .Value = DateSerial(Year(.Value) + 1, Month(.Value), Day(.Value))
> End With
> End If
> Next
> End Sub
>
> Thank you for your help,
> jfcby
>
> NickHK wrote:
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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 are:
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:(E-Mail Removed)...
> > > > > > > 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("B2")
> > > > > > > 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,

Thank you for your help! I'm new to VBA, only been using it for about 5
months now! I'll try and explain how the 2 worksheets come in.

My main BuildCalendar macro is how the 2 worksheets are referenced. I
put a spinner button on sheet6 (the calendar worksheet) to change the
year only. Sheet2 has all my dates and data that my main BuildCalendar
macro will insert the data. So, sheet6 is used only for the spinner
button, the macro codes DateTesterBaselineIncrease and
DateTesterBaselineDecrease had to be activated from another macro to
make the neccessary changes between the two worksheets.
Sub InDecreaseYear()
If Sheet2.Range("M4") = 1 Then
Call DateTesterBaselineDecrease 'Decrease year
End If
If Sheet2.Range("M4") = 2 Then
Call DateTesterBaselineIncrease 'Increase year
End If
Call BuildCalendar 'Main macro to setup calendar
End Sub

The reason why the .values relate to the same cell on worksheet2 is my
BuildCalendar macro uses those dates to insert the data for each day.
If I change the year in sheet2 row2 then run BuildCalendar macro only
the year changes.

I hope your questions were answered if not let me know and I'll be
happy to post my macros I used so that you can see how they relate.
Since my knowledge of vba is limited it shows up in my explanation but
I'm tring to learn more and this newsgroup is very helpful.

Thank you for all your help,
jfcby

NickHK wrote:
> Glad you got it working, but I don't see where the 2 worksheets come in.
> All the .values relate to the same cell, but...
>
> NickHK
>
> "jfcby" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello NickHK,
> >
> > Thank you for your help! The code you provided did not work the way I
> > needed so I found another code and modified it to work.
> >
> > Modified Working Code:
> >
> > Sub DateTesterBaselineIncrease()
> > Dim cell As Range
> > For Each cell In Worksheets(2).UsedRange.Columns(2).Cells
> > If cell >= DateSerial(1900, 1, 1) Then
> > With cell
> > .Value = DateSerial(Year(.Value) + 1, Month(.Value), Day(.Value))
> > End With
> > End If
> > Next
> > End Sub
> >
> > Thank you for your help,
> > jfcby
> >
> > NickHK wrote:
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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 are:
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > 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("B2")
> > > > > > > > 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
> > > > > > > >
> > > > > >
> > > >
> >