Hi,
Try this. Assumes sequence numbers in column A and outputs
Missing/duplicates in Columns A and B of second worksheet


Sub FindMissingAndDuplicates()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim v() As Long, missing() As Long, i As Long, lastrow As Long

sblock = Application.InputBox("Enter block start")
fblock = Application.InputBox("Enter block end")


ReDim v(fblock - sblock + 1)

j = 0
For i = sblock To fblock
v(j) = i
j = j + 1
Next i
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
ws2.Range("a1:b1") = Array("Missing", "Duplicated")

With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("a1:a" & lastrow)
End With

n1 = 2
n2 = 2
For i = LBound(v) To UBound(v)
If IsError(Application.Match(v(i), rng, 0)) Then
ws2.Cells(n1, 1) = v(i)
n1 = n1 + 1
Else
If Application.CountIf(rng, v(i)) 1 Then
ws2.Cells(n2, 2) = v(i)
n2 = n2 + 1
End If
End If
Next i
End Sub


"Ozzie via OfficeKB.com" wrote:

Hi Tony,

I need to do it in code.

Would the code be able to determine the missing numbers? ie if we had 65002
and 65005 would it produce the missing 65003 and 65004 or would it say that 2
number are missing? the first option is preferable.

Cheers for your help,

tony h wrote:
Depends really what you want to do:

a. if you want to do it in code than set up an integer array
dimensioned from the lowest value to the highest value. Then run
through the table add 1 using the value as an index to the array.
A value of 0 in an element of the array will indicate that it is
missing, a value of one that it occurs once and more than one indicates
duplicates.

2. if you want to do it on a spreadsheet. Then set up a table in the
same way. first cell is lowest value, Next cells are if lastcell+1 <
Max value then lastcell+1 else "". then in next column do a countA to
get number of occurances. A quick scan down the list will show where
there are problems.

Let us know what you want to do and I may post some code.

regards


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1