# finding missing invoices and recording them

#### gpapagre

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You want to find the numbers that dont exist ???????

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

Last edited:
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

Last edited:
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

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...

Code:
``````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

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...

Code:
``````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

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.

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

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

THANK YOU

Replies
18
Views
547
Replies
6
Views
201
Replies
4
Views
232
Replies
7
Views
120
Replies
58
Views
2K

1,203,206
Messages
6,054,146
Members
444,703
Latest member
pinkyar23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back