Stop For Each statement at first occurence

[theclaah]

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

 

[dk]

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

 

[theclaah]

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

 

[dk]

Try this:-

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