I have the range A:A named "case".
It contains the numbers 1001, 1002 etc...
I want a function to return the lowest number between 1001 and infinit
that IS NOT in the range "case"

I got this from ~x (thanks!) but I want to do this without using an
cells. Have tried goofing around with the below, but can't get it t
work...

~x wrote:

Assuming that your initial serial numbers are in A1:A5, insert thi
formula in A6
=getNAN(=getNAN($A$1:A6)

The getNAN UDF IS:

Public Function getNAN(ByVal MySerial As Range) As Variant
min = Application.WorksheetFunction.min(MySerial)
max = Application.WorksheetFunction.max(MySerial)
For i = 1 To max - min
If min + i < WorksheetFunction.Small(MySerial, i + 1) Then
getNAN = min + i
Exit Function
End If
Next i
If IsEmpty(getNAN) Then getNAN = max + 1
End Functio

--
Message posted from http://www.ExcelForum.com