Code execution has been interupted Problem

[guybrown]

hi all!

It sometimes highlights End With and sometimes highlights Range("C2:C10001").Select

Any idea what the problem might be?

Guy xp prof xl 2007


Sub Text_on_XY()

'AutoFill
Range("C2:C2").AutoFill Destination:=Range("C2:C" & Cells(Rows.Count, "B").End(xlUp).Row)
'Paste formula result as value
Range("C2:C10001").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Sort 1 2 3
Range("C2:D10001").Select
ActiveWorkbook.Worksheets("Text on XY").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Text on XY").Sort.SortFields.Add Key:=Range( _
"D2:D8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Text on XY").Sort
.SetRange Range("C2:D10001")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[Andrew Poulsom]

I've not seen an explanation for that, but maybe putting:

Application.EnableCancelKey = xlDisabled

at the beginning of your code will fix the problem.

 

[guybrown]

so far so good Andrew - how does that work?

 

[Andrew Poulsom]

It just prevents the pressing of Esc from interrupting the macro.

 

[guybrown]

well i wasn't pressing escape but it seems to have fixed it... cheers

 

[Stray__CAt]

It sounds like you hit the Esc key (or there was a hit pending) while your code was running.

In rare cases, anyway, VBA keeps stopping where you placed a breakpoint, even if the breakpoint has been subsequently deleted. In that case the only solution is to copy the module code, drop the module, insert another and copy the code back. It's a bug, no use to say.

 

[Andrew Poulsom]

In rare cases, anyway, VBA keeps stopping where you placed a breakpoint, even if the breakpoint has been subsequently deleted. In that case the only solution is to copy the module code, drop the module, insert another and copy the code back. It's a bug, no use to say.

That sounds like a plausible explanation, although I don't remember having set and removed a break point when it's happened to me. It's certainly some sort of bug.

 

[Colin Legg]

That sounds like a plausible explanation, although I don't remember having set and removed a break point when it's happened to me. It's certainly some sort of bug.

I've never had a ghost breakpoint happen to me in Excel VBA, but it has happened to me in MS Access VBA.

I listed a few other possible remedies here:
http://www.mrexcel.com/forum/showthread.php?t=373810