FIND POSITIVE VALUE

  • Thread starter FIRSTROUNDKO via OfficeKB.com
  • Start date
F

FIRSTROUNDKO via OfficeKB.com

Hi,

how do I select the first positive value in collumn K from row 1 downwards?

Thanks in advance

Darren
 
R

Ron Coderre

Try this:

=INDEX(K1:K100,MATCH(TRUE,(K1:K100>0),0))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
J

Jim May

one way (in a standard module) paste in:

Sub FindPositiveVal()
Dim lrow As Long
Dim MyRng As Range
Sheets("Sheet1").Range("K1").Select ' CHANGE Sheet Name
lrow = Cells(Rows.Count, 11).End(xlUp).Row
Set MyRng = Range("K1:K" & lrow)
For Each cell In MyRng
If ActiveCell.Offset(1, 0) >= 1 Then
ActiveCell.Offset(1, 0).Select
Exit Sub
Else: ActiveCell.Offset(1, 0).Select
End If
Next cell
End Sub

HTH
Jim May
 
Top