loop with two stops

ermccarthy

Board Regular
Joined
Feb 15, 2002
Messages
224
I have a quick little loop that I wrote to search for a certain value in a cell, but I need a "second stop" Sometimes on this report, it the item was not used in a particular day, it will not show up, therefore, my loop would run forever with out stopping, I would like it to stop when it reachs row 1000. Here is what I have so far....

Do Until Left(ActiveCell, 3) = Range _("seg1").Value

ActiveCell.Offset(1, 0).Select
Loop


I am stumped!!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
It looks like I can help you and you can help me.
Please view my routine as that will stop at the bottome of the range, but I cannot get it to select what I want.
KK
 
Upvote 0
Try inserting an IF statement. Something like:

Do Until Left(ActiveCell, 3) = Range _("seg1").Value

ActiveCell.Offset(1, 0).Select
If Selection.Row > 1000 Then Exit Loop
Loop
 
Upvote 0
it did not work, when typing in the new line, it gives me a compile error:
Expected: Do or For or Sub or Function or Property.

but I did type it in exactly....

If Selection.Row > 1000 Then Exit Loop
Loop

What am I doing wrong??
 
Upvote 0
On 2002-02-19 13:12, ermccarthy wrote:
it did not work, when typing in the new line, it gives me a compile error:
Expected: Do or For or Sub or Function or Property.

but I did type it in exactly....

If Selection.Row > 1000 Then Exit Loop
Loop

What am I doing wrong??
You need to type in all the code (sorry, it wasn't clear in the posting). Try this:
Code:
Do Until Left(ActiveCell, 3) = Range("seg1").Value 
ActiveCell.Offset(1, 0).Select 
If Selection.Row > 1000 Then Exit Loop 
Loop
 
Upvote 0
I believe that it's Exit Do (not loop), and you could also do something like this:

Do Until Left(ActiveCell, 3) = Range("seg1").Value Or Selection.Row > 1000
ActiveCell.Offset(1, 0).Select
'...
Loop

-rh
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top