try:

=FINDNUM(B1:B1000,123)



Function FindNum(ByRef rng As Range, Num As Long) As Long
res = Application.Match(Num, rng, 0)
If IsError(res) Then
FindNum = 0
Else
FindNum = res + rng(1).Row - 1
End If

End Function

"GeorgeJ" wrote:

Lets assume I have a column of numbers sorted in ascending order. Suppose
these numbers occupy the range B1:B1000. Suppose I want VBA code for

Function FindNum(Num as Long) as Long
[code]
End Function

which returns 0 if Num is not in column B and if it is returns the first row
in which Num is located. I realize I could write a loop to do this but is
there a faster, non-loop method? What about the case in which the numbers in
col B are not sorted?

Thanks

--
-regards