find first nonzero value in column
 

I have two columns A and B. Column A contains ascending numbers from 1 to
100, while Column B contains arbitrary values. I am looking for the function
that finds the first nonzero value in column B (going from first to last row)
and then returns the value of column A of the same row. (This request might
be posted twice).

David

Hi,

Try

Function NonZero(FirstCol As Range, SecondCol As Range)
Dim i As Integer
NonZero = Error

If (FirstCol.Rows.Count = SecondCol.Rows.Count) Then
For i = 1 To FirstCol.Rows.Count
If (SecondCol.Cells(i, 1) < 0) Then
NonZero = FirstCol.Cells(i, 1)
Exit For
End If
Next i
End If
End Function

This allows you to have separate ranges. The only thing you might want to
change is the return value if nothing is found.

HTH,

David Jessop

"DAVID" wrote:

I have two columns A and B. Column A contains ascending numbers from 1 to
100, while Column B contains arbitrary values. I am looking for the function
that finds the first nonzero value in column B (going from first to last row)
and then returns the value of column A of the same row. (This request might
be posted twice).

David

Hi,

On solution is:
=SMALL(IF(B1:B100<0,A1:A100),1)
Hold down Ctrl+Shift and then hit Enter to confirm the formula.

Ola