This post is actually in response to an Excel VBA question.
Basically it can be summarized by titling it “automatic invoice number generator excel”
That is what this is:
This can be filled out with user form, but all the data ultimately gets entered into the underlying spreadsheet so we’ll skip the user form part (let me know if you actually want to see it).
Column A is just a date (“TODAY’S DATE”).
Column B is a formula based on column A, “=YEAR(A3)”. (“YEAR”)
Column C is a formula based on column B, “=RIGHT(B2,2)”. (“LAST TWO DIGITS OF YEAR”)
Column D is just the “Month” function based on Column A, but the single digit months are padded “=IF(LEN(MONTH(A2))<10,"0" & MONTH(A2),MONTH(A2))". (“MONTH”)
and finally Column “E” is the “INVOICE NO” generated with the following code:
'code by loeblcomservices.com '713-409-7041 Sub Rectangle1_Click() Dim intActiveRow As Integer Dim lngLastRow As Long Dim strCustomerID As String Dim strInvoiceID As String intActiveRow = ActiveCell.Row lngLastRow = FindLastRow("A") strInvoiceID = GetNextInvoiceID(Range("C" & intActiveRow), Range("D" & intActiveRow)) 'write the new Invoice ID Range("E" & intActiveRow) = strInvoiceID End Sub Function FindLastRow(WhichColumn As String) As Long 'FINDS THE LAST ROW BASED ON THE COLUMN LETTER <--- Dim lngLastRow As Long 'move to the last row on the worksheet and find the last used cell. With ActiveSheet lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row End With FindLastRow = lngLastRow End Function Function GetNextInvoiceID(strYear, strMonth) As String Dim intCounter As Integer Dim varPos As Variant Dim lngLastRow As Long Dim strCounter As String Dim blnFound As Boolean lngLastRow = FindLastRow("A") 'invoice id's stop at 99, add more if you have more than 99 invoices per month For intCounter = 1 To 100 'pad the numbers under 10 If Len(intCounter) < 10 Then strCounter = "0" & intCounter Else strCounter = intCounter End If 'formulate the trial invoice id to search the list for strInvoiceID = strYear & strMonth & strCounter 'if the value is already in the list, the count will be greater than zero. intct = Application.WorksheetFunction.CountIf(Range("E1:E" & lngLastRow), strInvoiceID) If intct > 0 Then 'do nothing, because it already is used Else 'it's available, so use it and exit the function GetNextInvoiceID = strInvoiceID Exit For End If Next End Function
The real “work horse” here is the function “GetNextInvoiceID”.
It uses a for loop to search Column “E” for evidence that the invoice number was used, and it will use the next higher number instead.
What’s also worth noting is that you can use your normal Excel worksheet functions in your VBA by using, the Application.Worksheetfunction syntax: ex. Application.WorksheetFunction.CountIf
Let me know if you have questions.