Auto increase invoice number in this format "TE/2021-22/102"

Report
-
 murthy -
Hello,
please tell the vba code for increase invoice number to 103,leaving others same on opening worksheet


System Configuration: Windows / Firefox 89.0

4 replies

Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Murthy,

Use Right(your_value,3), then add 1 to it and then use the combination of Len and Left to chop of the 3 number and add the value you added 1 to.

Can you make that work?

Best regards,
Trowa
Hi TrowaD
Thank you very much for your immediate reply & help to my problem. As, i am not at all having knowledge about vba coding,can you please provide me complete code.
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Murthy,

Sure, that's what I normally do, but I was a bit short on time yesterday.

How do you see it happen/work?

The way I see it now, is that you have your invoice numbers in column B. The mentioned invoice number is in B2. The code below will ask you how many invoice numbers need to be created.

The code:
Sub RunMe()
Dim qAmount, rPart, x, y As Integer
Dim lPart As String

qAmount = InputBox("How many invoice numbers would you like to create?")

lPart = Left(Range("B2").Value, Len(Range("B2").Value) - 3)
rPart = Right(Range("B2").Value, 3)

For x = 3 To qAmount + 2
    y = y + 1
    Range("B" & x).Value = lPart & rPart + y
Next x
End Sub


Let us know what you like to see different.

Best regards,
Trowa

Hi TrowaD
Thank you very much for the code.My invoicen number is in B12 cell,i have changed the cell b2 to b12. i get yellow hight with right arrow mark before Sub RunMe(). hope you can guide me through this problem.

Sub RunMe()
Dim qAmount, rPart, x, y As Integer
Dim lPart As String

qAmount = InputBox("How many invoice numbers would you like to create?")

lPart = Left(Range("B12").Value, Len(Range("B12").Value) - 3)
rPart = Right(Range("B12").Value, 3)

For x = 3 To qAmount + 2
y = y + 1
Range("B" & x).Value = lPart & rPart + y
Next x
End Sub
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Murphy,

Have a look at code line 10:
For x = 3 To qAmount + 2

The 3 stands for the first row you want to start creating invoice numbers.
The 2 stands for the row your initial invoice number is located.

When you change the 'initial invoice number' cell from B2 to B12, then that code line should look like this:
For x = 13 To qAmount + 12


When you enter 5 as the number of invoice numbers you want to create, the creation starts at row 13 and ends at 17 (5+12).

Best regards,
Trowa
Hi Trowa
Thank you for your valuable time for explaining how it works but, i want my invoice number (in cell B12 & Row is 12) auto increment every time i open excel format for making new invoice & leaving the prefix "TE/2021-22/" same & open save as dialogue box to save an excel copy.

Thanks in advance
Murthy

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!