finding missing invoices and recording them

[gpapagre]

hello to everyone
as i find it very difficult to build formulas in excel i come up with your help

i just have column A with many invoice numbers that run sequentially. some of them are missing. wish to find them and place them in the next column.

thanks, god pless you

 

[SteveO59L]

You want to find the numbers that dont exist ???????

 

[NMeeker]

what do you mean by missing?

Do you mean the cell is blank?
or do you mean it skipped a number?

Blank Missing
1-----------1
2-----------2
3-----------3
4-----------5----4?
__--5-------6
6-----------8----7?
7-----------10---9?
8-----------11
9-----------12

 

[gpapagre]

HI, thank you

Yea, wish to record the missing invoices to the next column. example
column A
5200
5201
5202
5205
5206
5207
need to have 5203, 5204 (which are missing) written in column B or C

 

[gpapagre]

yes, it skipped a number or numbers.
example
column A
1
2
3? need to have numbers 4,5,6 writeen in next columns
7
8
9

 

[NMeeker]

Was actually expecting someone else to take this one...

If your invoices are ALL numbers, and not text, AND They are all sorted in numerical order, AND they can all be considered integers... Then this works great...

Sub MissingNumber()
Dim invoice As Integer, LastRow As Integer, cel As Range, CurRow As Integer, _
Rowvalue As Integer
LastRow = ThisWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
invoice = Range("A1").Value
CurRow = 1
For Each cel In Range("A1:A" & LastRow)
Again:
    If cel.Value = "" Then Exit Sub
    If Not cel.Value = invoice Then
        Range("B" & CurRow).Value = invoice
        CurRow = CurRow + 1
        invoice = invoice + 1
        GoTo Again
    End If
    invoice = invoice + 1
Next cel
End Sub

This assumes you have no header row, so I started it at row 1
Let me know how it works.. Good luck

 

[gpapagre]

Was actually expecting someone else to take this one...

If your invoices are ALL numbers, and not text, AND They are all sorted in numerical order, AND they can all be considered integers... Then this works great...

Sub MissingNumber()
Dim invoice As Integer, LastRow As Integer, cel As Range, CurRow As Integer, _
Rowvalue As Integer
LastRow = ThisWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
invoice = Range("A1").Value
CurRow = 1
For Each cel In Range("A1:A" & LastRow)
Again:
    If cel.Value = "" Then Exit Sub
    If Not cel.Value = invoice Then
        Range("B" & CurRow).Value = invoice
        CurRow = CurRow + 1
        invoice = invoice + 1
        GoTo Again
    End If
    invoice = invoice + 1
Next cel
End Sub

This assumes you have no header row, so I started it at row 1
Let me know how it works.. Good luck

Thanks for your time.
Well, it scared me at the beginning, I have managed to right click on X icon, go to view code and copy paste what you wrote here. Then I press Alt F11 to return to my worksheet. Stil have no idea how to activate this program.

 

[NMeeker]

Sorry for the delay, I have been out of town for the last week or so. If you are still trying to get this to work, write back and I'll see what I can do for you. Otherwise, this code was meant to placed in a module. You need use the developer tab... check Excel help to find out how to enable the developer tab.. or check the Internet more for other information about using VBA

 

[gpapagre]

I WISH TO THANK NMeeker FOR HIS VALUABLE ASSISTANCE.
HE DIDNT GIVE UP TILL I GOT IT RIGHT.

THANK YOU