Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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" wrote in message ups.com... 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" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error Message Excel 2000 - 2003
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" wrote in message ups.com... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 error message | New Users to Excel | |||
Excel 2000 error message | New Users to Excel | |||
Need Error Message Box In VBA Code - Excel 2000 & 2003 | Excel Programming | |||
Excel 2003 causes error with Excel 2000 VBA code | Excel Programming | |||
error message when opening excel 2000 | Excel Discussion (Misc queries) |