Hi everyone,
I have a Named Range (IdColumn) which contains a list of integer
values. This range varies from being blank, (containing no numbers) to
containing hundreds of values sorted Acsending. These values are always
whole numbers.
What I need is a routine for finding the lowest missing number. If the
Range is empty then return the value 1 in a msgbox. If it contains say
1,2,3 it would return 4 in a msgbox. If the range contained
1,2,3,4,5,6,7,8,9,11,12,13,15,16 it would return the lowest missing
value which would be 10 even though 14 is also missing.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=553292

Sub CheckIDColumn()
Dim rng as Range, m as Long, i as Long
set rng = Range("IDColumn")
if application.Count(rng) = 0 then
msgbox "1"
else
bDone = False
m = Application.Max(rng)
for i = 1 to m
if application.Countif(rng,i) = 0 then
msgbox i
bdone = True
exit for
end if
next
if not bDone then
msgbox m + 1
end if
End Sub

--
Regards,
Tom Ogilvy


"Casey" wrote:


Hi everyone,
I have a Named Range (IdColumn) which contains a list of integer
values. This range varies from being blank, (containing no numbers) to
containing hundreds of values sorted Acsending. These values are always
whole numbers.
What I need is a routine for finding the lowest missing number. If the
Range is empty then return the value 1 in a msgbox. If it contains say
1,2,3 it would return 4 in a msgbox. If the range contained
1,2,3,4,5,6,7,8,9,11,12,13,15,16 it would return the lowest missing
value which would be 10 even though 14 is also missing.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=553292

Tom,
Thank you. I had to Declare the bDone variable and I got a Block If
without End If error. Which probably means you typed this straight out
of your head on the fly. I would love to be able to do that. Thanks
again Tom, does just what i needed it to do.

Finished Code.
Sub CheckIDColumn()
Dim bDone As Boolean
Dim rng As Range, m As Long, i As Long
Set rng = Range("IDColumn")
If Application.Count(rng) = 0 Then
MsgBox "1"
Else
bDone = False
m = Application.Max(rng)
For i = 1 To m
If Application.CountIf(rng, i) = 0 Then
MsgBox i
bDone = True
Exit For
End If
Next i
End If
If Not bDone Then
MsgBox m + 1
End If
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=553292