Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: loop with two stops

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!

    Russell

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??
    Russell

  5. #5
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •