find first nonzero value in column

[DAVID]

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

 

[David Jessop]

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

 

[Ola]

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