I have 12,000 bank account numbers in sequential order in a sheet. How do I
go through the record and extract the missing account numbers?
Thanks.....

if the bank account numbers are sequential what constitutes a missing
account number?

if it is a blank row what do mean extract?

--
Cheers
Nigel



"mmc" wrote in message
...
I have 12,000 bank account numbers in sequential order in a sheet. How do
I
go through the record and extract the missing account numbers?
Thanks.....

Assume account numbers start in column A in row 2 and are numerical.
Missing numbers will be listed in Column J

Dim j as Long, a as Long
Dim FirstNum as Long, LastNum as Long
j = 1
a = 3
FirstNum = cells(2,1)
LastNum = cells(rows.count,1).End(xlup).Value
for i = FirstNum to LastNum
if cells(a,1) = i then
a = a + 1
else
cells(j,"J").Value = i
j = j + 1
end if
Next

--
Regards,
Tom Ogilvy


"mmc" wrote in message
...
I have 12,000 bank account numbers in sequential order in a sheet. How do
I
go through the record and extract the missing account numbers?
Thanks.....

the original incorrectly listed the first number as among the missing. Here
is a correction: lightly tested.

Sub BBBB()
Dim j As Long, a As Long
Dim FirstNum As Long, LastNum As Long
j = 1
a = 3
FirstNum = Cells(2, 1)
LastNum = Cells(Rows.Count, 1).End(xlUp).Value
For i = FirstNum + 1 To LastNum
If Cells(a, 1) = i Then
a = a + 1
Else
Cells(j, "J").Value = i
j = j + 1
End If
Next

End Sub


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Assume account numbers start in column A in row 2 and are numerical.
Missing numbers will be listed in Column J

Dim j as Long, a as Long
Dim FirstNum as Long, LastNum as Long
j = 1
a = 3
FirstNum = cells(2,1)
LastNum = cells(rows.count,1).End(xlup).Value
for i = FirstNum to LastNum
if cells(a,1) = i then
a = a + 1
else
cells(j,"J").Value = i
j = j + 1
end if
Next

--
Regards,
Tom Ogilvy


"mmc" wrote in message
...
I have 12,000 bank account numbers in sequential order in a sheet. How
do
I
go through the record and extract the missing account numbers?
Thanks.....

Thanks, Tom. It works.

"Tom Ogilvy" wrote:

the original incorrectly listed the first number as among the missing. Here
is a correction: lightly tested.

Sub BBBB()
Dim j As Long, a As Long
Dim FirstNum As Long, LastNum As Long
j = 1
a = 3
FirstNum = Cells(2, 1)
LastNum = Cells(Rows.Count, 1).End(xlUp).Value
For i = FirstNum + 1 To LastNum
If Cells(a, 1) = i Then
a = a + 1
Else
Cells(j, "J").Value = i
j = j + 1
End If
Next

End Sub


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Assume account numbers start in column A in row 2 and are numerical.
Missing numbers will be listed in Column J

Dim j as Long, a as Long
Dim FirstNum as Long, LastNum as Long
j = 1
a = 3
FirstNum = cells(2,1)
LastNum = cells(rows.count,1).End(xlup).Value
for i = FirstNum to LastNum
if cells(a,1) = i then
a = a + 1
else
cells(j,"J").Value = i
j = j + 1
end if
Next

--
Regards,
Tom Ogilvy


"mmc" wrote in message
...
I have 12,000 bank account numbers in sequential order in a sheet. How
do
I
go through the record and extract the missing account numbers?
Thanks.....