Auto generating invoice number based on invoice number on last row

[Raghav Chamadiya]

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:

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

 

[Oaktree]

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]

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]

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:

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