Why does macro randomly stop?

You don't say what your loops are. Possibly looping through each of the sheets in the workbook, each of the rows in each worksheet, each of the columns in each of the rows. How do you determine the upper limit of each loop? Do you use .SpecialCells? Do you use On Error Resume Next to cater for "anticipated" errors? If you do, it could be masking another error, or errors.

You can add Debug.Print (as has already been suggested) at various stages. Use some sort of marker to indicate which Debug.Print statement it is, and include the loop counters and the cell values or variables you are checking.

You can set break points so you can process whole sections of code without having to F8 through every line of code. Or you can use the Stop command to achieve the same effect.

Might be an idea to share your code and, ideally, sample workbook(s).

Regards, TMS

Hi shawnvw

Please post a Link to this...do the same on the other Forum.

Another forum suggested that it might be a memory problem

Do you have any Exit Sub or End statements?
Do you have any conditional formatting that uses UDFs?

Hi shawnvw

Please do this

Please post a Link to this...do the same on the other Forum.
Another forum suggested that it might be a memory problem

If you have answers on the "Other Forum" we're wasting our time. Not something most of us wish to do.

If you choose NOT to do this let us know. I'll not bother you again...though I MAY have a solution...perhaps...maybe.

Originally Posted by jaslake

Hi shawnvw

Please post a Link to this...do the same on the other Forum.

The other forum page is here: http://stackoverflow.com/questions/7...messages-occur

Most of the answer involved changing the code, but that wouldn't apply to me until I could tell where the processing stopped. One answer at the bottom suggested restarting Excel (which I had already tried) and moving the data into a new workbook.

Since then, I discovered there were two problems: certain cells, when selected or modified, would just shut down operation. I've moved the data to a new workbook, and I think that will solve that problem.

I still had the other problem: adding a new row to a ListObject stopped execution. I changed the table to an ordinary range, then back to a table again, and that has cleared it up.


So, if anyone else is having this problem: there's a chance it's not the code that's your problem, but corruption on your sheets. Good luck.