Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: For i - 1 to Something loop

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the following routine. In it I am looking for the first cell that contains the word NO ENTRY. The first time I find this value, I would like to leave this routine without doing the entire 10000 passes. Is there a way to do that?

    For i = 1 To 10000
    Select Case Range("BT3").Offset((i - 1), 0).Value
    case Is = "ENTRY"
    Range("BQ3").Offset((i - 1), 0).Select
    Case Else
    Range("BQ3").Offset((i - 1), 0).Select
    Selection.ClearContents
    End Select
    Next i

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you are just trying to find "No Entry" you could just do a find or is there more that your not telling me>?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to find the first NO ENTRY and then use the offset ((1-i... at that point to modify the cell next to the point that I find the entry.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In other words, once I find that point I want to do an operation at that point then leave the loop, not go thru all 10000 locations.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could try
    Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    to find 'No Entry' then offset (move to the next column) and modify your entry.

    any help?

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow, Brett, That one is a little over my head. I am a total novice and thought finding the i = loop was an accomplishment. I can play with what you gave me a bit and see if I can make it work. How do I specify the starting point to begin looking in it?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it will just find the first one on the page top to bottom so you could specify a start location of
    range("a1").select if you wish to start at the top.


  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I selected Cell BT3 then put in your command line. when I ran it, it stopped with Compile error, named arguement not found. The SearchFormat:= was highlighted. ?????

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 16:35, brettvba wrote:
    you could try
    Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    to find 'No Entry' then offset (move to the next column) and modify your entry.

    any help?
    Range("BT3").Select
    Cells.Find(What:="No Entry", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select

    should work what version are you using



    [ This Message was edited by: brettvba on 2002-04-29 17:15 ]

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sorry, Brett, but I get the same thing. Compile error, Named arguement not found. SearchFormat:= highlighted

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
  •