ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Stepping through Code (https://www.excelbanter.com/excel-discussion-misc-queries/163433-stepping-through-code.html)

Jim May
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

JLatham
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

Jim May
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

joel
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

Dave Peterson
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

Jim May
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

Jim May
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

joel
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

JLatham
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

Jim May
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


All times are GMT +1. The time now is 05:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com