Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop problem
Hi
Is there a limited time a for next loop will run? (not counted). The end of the data is marked by text "END". This works fine for small amounts of data, but seems to exit around 96 samples. If I then re-run it its fine and exits correctly. Thanks Jo On Error GoTo errhandler Dim endofdata For Each cell In Range("results") Range("O7").Select endofdata = ActiveCell If endofdata = "END" Then GoTo errhandler various stuff here (cells get deleted and moved up) Next errhandler: Exit Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop problem
"Jo" wrote...
Is there a limited time a for next loop will run? (not counted). The end of the data is marked by text "END". This works fine for small amounts of data, but seems to exit around 96 samples. If I then re-run it its fine and exits correctly. ... various stuff here (cells get deleted and moved up) Next ... If you're deleting cells, better to iterate from bottom to top or right to left. So don't use a For Each loop, use Dim n As Long ': For n = Range("Results").Rows.Count To 1 Step -1 ': -- To top-post is human, to bottom-post and snip is sublime. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop problem
Jo,
There certainly is no limit in that way. What determines it is the size of the range that you are iterating through. I would suggest though that bombing out of the loop the way that you do is not a good practice. It is probably caused by deleting stuff as you go through, as this can play havoc with the pointers. It could then work a second time as the first pass has cleared the path so to speak. In this instance I would suggest an approach like this On Error GoTo errhandler Dim cell As Range Dim rng As Range For Each cell In Range("results") If cell.Value = "END" Then Exit For Else If cell.Value = "a value to be deleted" If rng Is Nothin g Then Set rng = cell Else Set rng = Union(rng, cell) End If End If Next If Not rng Is Nothing Then rng.Entirerow.Delete End If errhandler: Exit Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jo" wrote in message ... Hi Is there a limited time a for next loop will run? (not counted). The end of the data is marked by text "END". This works fine for small amounts of data, but seems to exit around 96 samples. If I then re-run it its fine and exits correctly. Thanks Jo On Error GoTo errhandler Dim endofdata For Each cell In Range("results") Range("O7").Select endofdata = ActiveCell If endofdata = "END" Then GoTo errhandler various stuff here (cells get deleted and moved up) Next errhandler: Exit Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do while loop problem | Excel Discussion (Misc queries) | |||
Loop Problem | Excel Programming | |||
Problem with Loop | Excel Programming | |||
For..Next loop problem | Excel Programming | |||
loop problem | Excel Programming |