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.

Regards,
WiseOwl ExcelAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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

0
 
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
0
 
Rob HensonFinance AnalystCommented:
You can get the last number with an array formula:

=MAX(RIGHT(A1:A4,4)*1)

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:

="INV"&MAX(RIGHT(A1:A4,4)*1)+1

Again confirmed with "Shift + Ctrl + Enter".

Thanks
Rob
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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.

Regards,

'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
0
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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.
0
 
WiseOwl ExcelAuthor Commented:
Thanks!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.