Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
As I step thru the following code - after doing the F8 on the line:
..Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Can we see all of the code, from Sub to End Sub?
"Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Sub SumRange()
Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
You are probably getting an error. Comment out the ON Error and see what
error you do get. Also put a break point in the ON Error routine. Your On Error function could also have errors that need to be fixed. "Jim May" wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Do you have merged cells in that JimsSummary worksheet?
Is that worksheet protected? Jim May wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Joel,
If I comment out the On Error GoTo wsAdd and step through the same thing occurs. The entire Macro just stops -- the yellow highlighter disappears and the cursor remains in position 1 on the line .Cells.ClearContents. Pressing F8 again causes the My Line 1 to be highlighted (My Sub SumRange() I put a breal-point on the Line - With Sheets("JimsSummary") and was able to step through to that same line without being redirected to the wsAdd label (line). Jim "Joel" wrote: You are probably getting an error. Comment out the ON Error and see what error you do get. Also put a break point in the ON Error routine. Your On Error function could also have errors that need to be fixed. "Jim May" wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Dave:
Thanks, but No -- No merged cells in JimsSummary Neither is the sheet or book Protected. Jim "Dave Peterson" wrote: Do you have merged cells in that JimsSummary worksheet? Is that worksheet protected? Jim May wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Check for worksheet macros and This workbook macros. You may have and event
running another macro. Add break points at the start of these functions to find out whatt is happening. There may be an error in these macros. If there is an On error statement in these macros they will stop your macro from completing. "Jim May" wrote: Dave: Thanks, but No -- No merged cells in JimsSummary Neither is the sheet or book Protected. Jim "Dave Peterson" wrote: Do you have merged cells in that JimsSummary worksheet? Is that worksheet protected? Jim May wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
I can't really think of anything to add that hasn't already been said by Joel
or Dave. My thought is that there is quite possibly some _Change() event code for that worksheet that is being called each time a cell on it is getting cleared with the .Cells.ClearContents statement. Ok, maybe a couple of things to add. First you might just insert a new, empty sheet to the workbook and then change the sheet referenced in the code to its name and step through and see if it still hangs. Since you know there isn't any code in a new sheet, that would help eliminate that side of things. Although there could be code in This Workbook intercepting all/any sheet events. Another thing to try, without changing sheet name in the code would be to disable event processing just before that section of code. Rewrite it as: .... Application.EnableEvents = False With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With Application.EnableEvents = True .... "Jim May" wrote: Dave: Thanks, but No -- No merged cells in JimsSummary Neither is the sheet or book Protected. Jim "Dave Peterson" wrote: Do you have merged cells in that JimsSummary worksheet? Is that worksheet protected? Jim May wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stepping through Code
Thanks BOTH Joel and JLatham;
I'm now off to work but will pursue your instructions tonight. Have a great day !! Jim May "JLatham" wrote: I can't really think of anything to add that hasn't already been said by Joel or Dave. My thought is that there is quite possibly some _Change() event code for that worksheet that is being called each time a cell on it is getting cleared with the .Cells.ClearContents statement. Ok, maybe a couple of things to add. First you might just insert a new, empty sheet to the workbook and then change the sheet referenced in the code to its name and step through and see if it still hangs. Since you know there isn't any code in a new sheet, that would help eliminate that side of things. Although there could be code in This Workbook intercepting all/any sheet events. Another thing to try, without changing sheet name in the code would be to disable event processing just before that section of code. Rewrite it as: ... Application.EnableEvents = False With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With Application.EnableEvents = True ... "Jim May" wrote: Dave: Thanks, but No -- No merged cells in JimsSummary Neither is the sheet or book Protected. Jim "Dave Peterson" wrote: Do you have merged cells in that JimsSummary worksheet? Is that worksheet protected? Jim May wrote: Sub SumRange() Dim myCells As String Dim wsn As Integer Dim Wst As Worksheet Dim answer As Double Dim myRange As Range Dim lrow As Long On Error GoTo wsAdd With Sheets("JimsSummary") .Activate .Cells.ClearContents ' Problem here !!! End With For Each Wst In Worksheets If Left(Wst.Name, 3) = "Day" Then CurrWs = Wst.Index Wst.Select Exit For End If Next myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address If myCells = "" Then 'user hit cancel Exit Sub End If 'Sheets("Sheet1").Activate Application.ScreenUpdating = False Sheets("JimsSummary").Activate rwct = 1 For DayNoSh = CurrWs To CurrWs + 30 Cells(rwct, 1) = Sheets(DayNoSh).Name Set myRange = Sheets(DayNoSh).Range(myCells) answer = Application.WorksheetFunction.Sum(myRange) Cells(rwct, 2).Value = answer rwct = rwct + 1 Next DayNoSh 'End If lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")" Range("B:B").NumberFormat = "0,000.00" GoTo finish wsAdd: Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "JimsSummary" On Error GoTo 0 Resume Next finish: Application.ScreenUpdating = True End Sub TIA, Jim "JLatham" wrote: Can we see all of the code, from Sub to End Sub? "Jim May" wrote: As I step thru the following code - after doing the F8 on the line: .Cells.ClearContents (below) ,,, It Stops - the cursor remains on this line - when I press F8 a second time the cursor jumps back to my Sub Name Line 1 Any ideas what's happening? With Sheets("JimsSummary") .Activate .Cells.ClearContents End With -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I define X-axis stepping? | Charts and Charting in Excel | |||
stepping chart | Charts and Charting in Excel | |||
Stepping Through vs. Play | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) |