Auto generating invoice number based on invoice number on last row

Raghav Chamadiya

New Member
Joined
May 31, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
So, I have a userform and a database file, the format of the invoice numbers(which act like unique serial numbers) in the database goes like 001/2020, 002/2020, 003/2020 and so on.
With the help of Userform_Initialize, I am auto populating the invoice field in my userform. So if last invoice is 008/2020, the invoice in form will show as 009/2020.

The code to do that is:

VBA Code:
Me.txtInvoice.Value = Format(Int(Left(lastInvoice, 3)) + 1, "000") & "/" & current_year

where lastInvoice is the Invoice number in the last used row in database, this is working fine.

The problem now is that sometimes users need to change the invoice numbers to the format xxx-A/2020, xxx-B/2020, and so on. xxx can be any number which is getting auto generated.
So when they add this A, B, C, my code stops calculating the invoice number properly.
Also they need not add the A, B, C every time. So, in database it can xxx/year or xxx-A/year and so on

Please help
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,996
I'm not sure what your question is. Is it:

(1) That you want to ignore xxx-A/2020 and add one to the last invoice number that didn't have an A/B/C
(2) That you want to give users the ability to add an A/B/C to the invoice number that your code generates
(3) That you want to give users the ability to manually enter an xxx-A/2020 invoice number instead of using the auto generated one

or something else?
 

Raghav Chamadiya

New Member
Joined
May 31, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Its point one actually, I want to ignore the A, B, C and add one to the last invoice which didnt have A/B/C
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,996
To find the right value, you can search for the wildcard string of "???/????". As long as you're trying to match against the entire cell's contents, that will ignore the A/B/C ones.

By setting the searchdirection to be xlPrevious, it will search from the bottom of your range to the top, which should be just what you need.

Putting that together, try:
Code:
Set lastInvoice = Range("A:A").Find(what:="???/????", lookat:=xlWhole, searchdirection:=xlPrevious).value
Me.txtInvoice.Value = Format(Int(Left(lastInvoice, 3)) + 1, "000") & "/" & current_year
 

Watch MrExcel Video

Forum statistics

Threads
1,127,454
Messages
5,624,846
Members
416,062
Latest member
NIDHYAMARI

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top