Hmmm....

Just thought of something.

lngMax = Application.Max(rngData)

This seems to assume that the max value in the range is the upper boundry of
the sequence. What if the upper boundry is one of the missing values?

For example, the sequence is 1 to 110 but the max value in the range is 100.

Biff

"JMB" wrote in message
...
So you have a column of consecutive numbers, but some of those numbers are
missing and you want to know which ones are missing (listed in column D of
the same worksheet)?

Be sure to backup your work first. Be sure the worksheet w/your data is
the
active sheet, change the range as needed, and change the 1 in the for loop
to
whatever is the lowest number that is supposed to appear in your data set.


Sub test()
Dim lngMax As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000")
lngMax = Application.Max(rngData)
lngcount = 1

For i = 1 To lngMax
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i
lngcount = lngcount + 1
End If
Next i

End Sub


"sa02000" wrote:


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