Excel With VBA

Automatic Invoice Number Generator Excel VBA Project

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.

admin