Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Hi,
For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
First, if wsOld is part of the WbkOld workbook, you don't need code like:
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Dave,
I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
If you were getting a "subscript out of error" message, I bet dollar to
doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Dave,
I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
I select the variable in the procedure that I want to watch.
Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Dave,
Ok, I got it. I have to select the procedure that declared the variable. I found the sort problem and it has been fixed. But I am still erroring at the Lusedrow, so they were unrelated. -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Dave,
I found if I activate wsOld at the beginning of the Lusedrow procedure everything works fine. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I select the variable in the procedure that I want to watch. Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
I don't see a lusedrow procedure anywhere in your messages.
Karen53 wrote: Dave, I found if I activate wsOld at the beginning of the Lusedrow procedure everything works fine. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I select the variable in the procedure that I want to watch. Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Can you post the portion of the code that fails?
Remember to include any values that the the procedure depends on. Karen53 wrote: Dave, Ok, I got it. I have to select the procedure that declared the variable. I found the sort problem and it has been fixed. But I am still erroring at the Lusedrow, so they were unrelated. -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Hi Dave,
Sub IDLocs, the first one I posted where it kept erroring out at the Lusedrow line. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I don't see a lusedrow procedure anywhere in your messages. Karen53 wrote: Dave, I found if I activate wsOld at the beginning of the Lusedrow procedure everything works fine. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I select the variable in the procedure that I want to watch. Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
This line causes an error?
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row I don't see anything that looks bad. Is this code in a general module or in the ThisWorkbook module or a behind a worksheet? If yes, you may want to try qualifying Rows: with wbkOld.sheets(wsold.name) ' with wsold looks nicer! Lusedrow = .Cells(.Rows.Count, "B").End(xlUp).Row end with Karen53 wrote: Hi Dave, Sub IDLocs, the first one I posted where it kept erroring out at the Lusedrow line. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I don't see a lusedrow procedure anywhere in your messages. Karen53 wrote: Dave, I found if I activate wsOld at the beginning of the Lusedrow procedure everything works fine. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I select the variable in the procedure that I want to watch. Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15") End If End sub -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variables
Hi Dave,
I think the two problems (sort & Lusedrow) may have been bumping into each other accounting for the first time it errored. Also, I had the format on the Lusedrow changed incorrectly to Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I see from your example it needs the with. Somehow I missed that before. Thanks so much. -- Thanks for your help. Karen53 "Dave Peterson" wrote: This line causes an error? Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row I don't see anything that looks bad. Is this code in a general module or in the ThisWorkbook module or a behind a worksheet? If yes, you may want to try qualifying Rows: with wbkOld.sheets(wsold.name) ' with wsold looks nicer! Lusedrow = .Cells(.Rows.Count, "B").End(xlUp).Row end with Karen53 wrote: Hi Dave, Sub IDLocs, the first one I posted where it kept erroring out at the Lusedrow line. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I don't see a lusedrow procedure anywhere in your messages. Karen53 wrote: Dave, I found if I activate wsOld at the beginning of the Lusedrow procedure everything works fine. -- Thanks for your help. Karen53 "Dave Peterson" wrote: I select the variable in the procedure that I want to watch. Then I hit shift-f9 to add a quick watch. If the variable that I selected is an object (like a range or worksheet or ...), then I'll go to the immediate window (ctrl-g) and select that variable. Then I'll add the property that I want. MyCell.value or mycell.address Or when you're stepping through the code, you'll see that + in front of the watched variable. You can expand that as much as you like. Karen53 wrote: Dave, I was mistaken. The last error on the sort code was 'Application defined or object defined error.' I need some guidance with the Watch Window. I have my variables set up but some of them do not change once the value has been defined, most of them. The only ones that change are the worksheets and workbooks. I think perhaps I set it up wrong? I just added the variables. I have "All Procedures" and the module name in the context boxes. For Watch Type I selected 'Watch Expression.' The watch window says "Expression not defined in context". What do I need to do? -- Thanks for your help. Karen53 "Dave Peterson" wrote: If you were getting a "subscript out of error" message, I bet dollar to doughnuts that you had something wrong--a typo or an unqualified reference. I don't recall ever getting that error when it wasn't my fault. When you're debugging your code, you can add some of those variables to the watch window and step through the code while keeping an eye on those variables. It might make things a little clearer than the debug.print statements. Karen53 wrote: Dave, I'll try that. In stepping through the code I recall everything being correct, but I ccould be remembering incorrectly. The code doesn't get as far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. I suspect the problem is actually elsewhere. It's so frustrating when the errors point everywhere but where the error actually is. I had code to sort the range set to run prior to this which would be the 'Advisor' section and that was erroring out at well. The sort was giving me subscript out of range when it wasn't. I removed it to try and pinpoint the problem. Then 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. Just to cover these bases, here is the code for my global variables and setting wsOld(thanks to you!)... Option Explicit 'module modCopyData Public wsNew As Worksheet Public wsOld As Worksheet Public wbkOld As Workbook Public wbkNew As Workbook Public OldJustPath As String Public OldwbkPath As Variant Sub SetWkShtVariable(OldShName) Dim wks As Worksheet Debug.Print "Starting SetwkShtVariable" Set wsOld = Nothing Set wks = Nothing For Each wks In wbkOld.Worksheets If LCase(wks.CodeName) = LCase(OldShName) Then 'found it Set wsOld = wks Exit For 'stop looking End If Next wks If wsOld Is Nothing Then 'not found! Else MsgBox wsOld.Name & vbLf & wsOld.CodeName Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _ & wsOld.CodeName End If End Sub Here is the code calling the procedures. If I leave the sort in it errors at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData", of calls. For iCtr = 0 To 2 Debug.Print "Start " & SheetRangearray(iCtr) DataType = "ThisData" OldShName = SheetRangearray(iCtr) Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call SetWkShtVariable(OldShName) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" DataType = "ThisVolume" Call CrackedID(OldShName, FileName, DataType, StartCol, _ EndCol, SumCol, StartRow, EndRow) Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _ rngFormatDateCell) Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _ DataType, StartCol, EndCol, StartRow, EndRow) Call SetIDGraphs(OldShName, EndRow) DataType = "" OldShName = "" Next -- Thanks for your help. Karen53 "Dave Peterson" wrote: First, if wsOld is part of the WbkOld workbook, you don't need code like: Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row you can refer to wsold directly Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row I think it makes the code easier to read (and write). Second, I don't see anything that jumps out with a problem. But that doesn't mean much. The suggestion I would offer is to add a debug.print line right before the line that causes the error. But print all the variables and their values so you know what's going on. So if this line is causing the trouble: Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _ & NLusedrow + Lusedrow) Or Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow) Then add this before that line: debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow Maybe something is not returning the value you think. Karen53 wrote: Hi, For some reason the "Advisor" sections run fine. As soon as the ws name changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld and wbkOld are set. Debug gives me the correct names. Is there something about global variables I've missed? Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell) Dim Lusedrow As Long Dim NLusedrow As Long 'Advisor If wsOld.Name = "Advisor" And DataType = "ThisData" Then Debug.Print "Starting Advisor ThisData " & DataType 'get the last used row Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Debug.Print "Advisor ThisData Lusedrow " & Lusedrow Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2) Lusedrow = 0 End If 'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _ & DataType If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then Debug.Print "Starting Advisor ThisVolume " & DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp 'add the new month data Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15") Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15") End If Debug.Print "wbkOld.name " & wbkOld.Name 'Annuity If wsOld.Name = "Annuity" And DataType = "ThisData" Then Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType 'get the last used row ***** Error is here ***** Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _ End(xlUp).Row Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow) NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _ End(xlUp).Row NLusedrow = NLusedrow + 2 'move past last item and leave 1 space 'between Advisor and Annuity Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _ & ":C" & NLusedrow + Lusedrow) Lusedrow = 0 End If If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _ DataType Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C1:D1") 'remove the lapsed month wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
global variables | Excel Programming | |||
Global variables | Excel Programming | |||
global variables | Excel Programming | |||
Global Variables | Excel Programming | |||
global variables | Excel Programming |