P.S.

If you want an error trap: (makes the formula almost twice as long)

=IF(ROWS($1:1)<=SUMPRODUCT(--(ISNA(MATCH(ROW($1:$5000),C$1:C$4000,0)))),INDEX(R OW($1:$5000),SMALL(IF(COUNTIF(C$1:C$4000,ROW($1:$5 000))=0,ROW($1:$5000)-ROW(A$1)+1),ROWS($1:1))),"")

Biff

"Biff" wrote in message
...
Hi!

You can do this with a formula as long as the max number in the sequence
is <=65536.

Suppose your sequence is from 1 to 5000 with various numbers missing and
is in the range C1:C4000.

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW($1:$5000),SMALL(IF(COUNTIF(C$1:C$4000,R OW($1:$5000))=0,ROW($1:$5000)-ROW(A$1)+1),ROWS($1:1)))

Copy down until you get #NUM! errors.

Biff

"sa02000" wrote in
message ...

I have 4000 rows of data with numbers in it (only one column...column
C). These numbers are consecutive but these have breaks in between. So
I am trying to find out where those breaks are and output those points
in a list. So here is a macro that I came up with...but these array
doesn't seem to write out results.... can anybody help please?

This macro tries to compare the two adjacent cells and if the
difference between the values is greater than 1 then it copies that
value in an array and writes that value in a cell in the same sheet.

I am no expert by any means. Please help!

Sub Macro1()
Dim temp(1, 1000) As Integer
i = 0
previouscell = 0
For Each cell In Range("C:C")
currentcell = cell.Value
abc = previouscell + 1
If currentcell < abc Then
temp(1, i) = currentcell
i = i + 1
Range("D" & i).Value = temp(1, i)
End If
previouscell = cell.Value

Next cell

End Sub


Thanks, Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
View this thread:
http://www.excelforum.com/showthread...hreadid=569539