Generating invoice number automatic without repetition

Hi Solomon,

Let's say that the 'cell next to it' is column A and the invoice numbers are placed in column B. Manually enter your first invoice number in B2. Then after implementing the code below and entering a value in A3, the code will pick the value from B2 and adds 1 to it. Skipping a row will mess up how the code works.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Or Target.Row < 3 Then Exit Sub

Target.Offset(0, 1).Value = Target.Offset(-1, 1).Value + 1
End Sub

The reference to column A can be changed on code line 2.

The reference to column B can be changed on code line 5, by changing both 1's after both comma's. 1 means 1 cells to the right of column A.

If you are looking for a different answer then please specify in detail.

Best regards,

Trowa

Hi Solomon,

Give the following code a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Or Target.Row < 7 Or (Target.Row - 2) Mod 5 <> 0 Then Exit Sub

Target.Offset(0, 1).Value = Target.Offset(-5, 1).Value + 1
End Sub

Manually enter your first invoice number in B2 as before, but now column B will only get an incremental invoice number when something is entered in cell A7, A12, A17 etc. (so 4 empty cells/rows in between).

Best regards,

Trowa