Generating invoice number automatic without repetition

Solved
SOLOMON231 Posts 2 Registration date Tuesday August 23, 2022 Status Member Last seen August 23, 2022 - Aug 23, 2022 at 07:46 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Aug 25, 2022 at 11:59 AM

Hello,

please I need help , I want VBA CODE  to Generating invoice number  automatic without repetition in my excel any time type in the next cell it.

Thanks

2 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 23, 2022 at 11:56 AM

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


1
SOLOMON231 Posts 2 Registration date Tuesday August 23, 2022 Status Member Last seen August 23, 2022
Aug 23, 2022 at 06:01 PM

Thank you for the quick response. I appreciate. The code work but i wish to skip like 4 rows before the invoice number, because i used group and ungroup function in my sheet.

0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 25, 2022 at 11:59 AM

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


0