Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Stop For Each statement at first occurence

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How to I stop a For Each statment at the first occurence?
    Or should I use a "find" somehow?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I take it you want to refer to the first item in the collection. For example, if you want to refer to the first worksheet in the worksheets collection just do this:-

    Set MySheet = Worksheets(1)

    This is the equivalent of doing this:-

    For each w in Worksheets
    Set MySheet=W
    Exit For
    Next
    Msgbox Mysheet.name

    HTH,
    Dan


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Will actually I have a riange of data and I want to find the first occurence of a value less than zero.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:-

    Code:
    Sub FindLessThanZero()
    Dim rngeCell As Range, rngeFound As Range
    
    For Each rngeCell In Range("A1:A1329")
        If rngeCell.Value < 0 Then Set rngeFound = rngeCell: Exit For
    Next rngeCell
    
    If Not rngeFound Is Nothing Then
        MsgBox "The first cell with a value less than zero is " & rngeFound.Address
    Else
        MsgBox "No cells were found in the specified range."
    End If
    
    End Sub
    HTH,
    Dan

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
  •