Report on missing numbers
 

Jas,

Try some code like the following:

Sub AAA()

Dim LastRow As Long
Dim FirstRow As Long
Dim RowNdx As Long
Dim N As Long
Dim TestColumn As Long
Dim WS As Worksheet
Dim DestinationRange As Range
Dim Rng As Range

TestColumn = 1 '<<< CHANGE AS REQUIRED
FirstRow = 1 '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
Set DestinationRange = Worksheets("Sheet2").Range("A1") '<<< CHANGE AS
REQUIRED

With WS
LastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row
For Each Rng In .Range(.Cells(FirstRow, TestColumn), _
.Cells(LastRow - 1, TestColumn)).Cells
If Rng.Value < Rng(2, 1).Value - 1 Then
For N = Rng.Value + 1 To Rng(2, 1).Value - 1
DestinationRange.Value = N
Set DestinationRange = DestinationRange(2, 1)
Next N
End If
Next Rng
End With

End Sub


If you actually want to insert rows or cells and fill in the missing values
of the series, see the code on
http://www.cpearson.com/excel/InsertAndFill.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Jas" wrote in message
...
Hi, I receive a report of several hundred invoices that are numbered
sequentially and sometimes there are numbers that are missing in a
sequence.
Is there a way that I can run a macro and it will tell me in a different
sheet exactly what numbers are missing?

Thanks!

Report on missing numbers
 

Thanks! That worked a treat!

"Chip Pearson" wrote:

Jas,

Try some code like the following:

Sub AAA()

Dim LastRow As Long
Dim FirstRow As Long
Dim RowNdx As Long
Dim N As Long
Dim TestColumn As Long
Dim WS As Worksheet
Dim DestinationRange As Range
Dim Rng As Range

TestColumn = 1 '<<< CHANGE AS REQUIRED
FirstRow = 1 '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
Set DestinationRange = Worksheets("Sheet2").Range("A1") '<<< CHANGE AS
REQUIRED

With WS
LastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row
For Each Rng In .Range(.Cells(FirstRow, TestColumn), _
.Cells(LastRow - 1, TestColumn)).Cells
If Rng.Value < Rng(2, 1).Value - 1 Then
For N = Rng.Value + 1 To Rng(2, 1).Value - 1
DestinationRange.Value = N
Set DestinationRange = DestinationRange(2, 1)
Next N
End If
Next Rng
End With

End Sub


If you actually want to insert rows or cells and fill in the missing values
of the series, see the code on
http://www.cpearson.com/excel/InsertAndFill.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Jas" wrote in message
...
Hi, I receive a report of several hundred invoices that are numbered
sequentially and sometimes there are numbers that are missing in a
sequence.
Is there a way that I can run a macro and it will tell me in a different
sheet exactly what numbers are missing?

Thanks!