Get Unique invoice Number for Unsorted or Non Sequential Data VBA Excel

Hellow Experts,

As per my Question Tittle Get Unique invoice Number for Unsorted or Non Sequential Data VBA Excel i need help that i have Invoice numbers randomly in Column A as mentioned below unsorted or non sequentially  and i need next invoice number Please advice My Required Invoice number to Populate in textbox while initializing Userfomr is INV10005

Column A

1 INV1001
2 INV1003
3 INV1004
4 INV1002

Please advice with best code and Oblige.

WiseOwl ExcelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Give this a try...
Private Sub UserForm_Initialize()
Dim wsData As Worksheet
Dim lr As Long, i As Long
Dim x, y()
Dim NextInvoice

Set wsData = Sheets("Sheet1")       'Change the data sheet if required
lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row
x = wsData.Range("A2:A" & lr).Value 'Assuming Invoice# starts from Row2
ReDim y(1 To UBound(x, 1))
For i = 1 To UBound(x, 1)
    y(i) = Mid(x(i, 1), 4, 255) * 1
Next i
NextInvoice = Application.Max(y) + 1
NextInvoice = "INV" & NextInvoice
Me.TextBox1.Value = NextInvoice     'Change the name of TextBox if required
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Create a Custom Number Format for the column containing the numbers - "INV"0000

Custom Number Format
  1. Select the column for which you want to create the custom formatting, and press Ctrl+1 to open the Format Cells dialog.
  2. Under Category, select Custom.
  3. Type the format code in the Type box i.e use "INV"0000.

Now "INV" will appear in front of any number entered without affecting the numbers.

This code demonstrates how to get the next sequential number, although you could use the formula directly in the sheet
Rob HensonFinance AnalystCommented:
You can get the last number with an array formula:


Confirm with "Shift + Ctrl + Enter" and the formula will be enclosed within curly brackets {  }  in the formula bar.

Based on that you can then use the following to determine next invoice:


Again confirmed with "Shift + Ctrl + Enter".

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

WiseOwl ExcelAuthor Commented:
Respected Subodh Tiwari (Neeraj)

I Have Tested Your Given Code and Working Fine for me However Please kindly Explain me following Lines for My Education and Understanding.


'what Redim Does and Ubound Do here
ReDim y(1 To UBound(x, 1))
For i = 1 To UBound(x, 1)
    y(i) = Mid(x(i, 1), 4, 255) * 1
Next i
NextInvoice = Application.Max(y) + 1
NextInvoice = "INV" & NextInvoice
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
With ReDim you dynamically set the array's size. You should read about Arrays to have a good understanding about them.
Within For Loop we are storing the numeric part of Invoice ID into the array y.
The first NextInvoice statement is trying to get the max numeric part used and adding 1 to it to get the next numeric part of the invoice.
The second NextInvoice statement is constructing the Invoice ID by concatenating the string INV with the next possible numeric part.
WiseOwl ExcelAuthor Commented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.