CODEWITHSUNDEEP
excelvba
Raghav Chamadiya
Raghav Chamadiya

Reputation: 240

Reset the automatic invoice generation to 000/'current year' at the start of a new fiscal year in Excel VBA

I have a working code which automatically increments invoice number in the format 112/2020, 113/2020, and so on (for the fiscal year 2020) when the user inputs other details in a userform and hits submit.

What I want is to reset this invoice number to 001/2021 as soon as the fiscal year changes in the month of April according to my fiscal year function. And it should do that every year when the fiscal year increments.

The function I am using to calculate current fiscal year is given below

If Month(dDate) <= 3 Then
    FinancialYear = Year(dDate) - 1
  Else
    FinancialYear = Year(dDate)
  End If
End Function

I am incrementing the invoice numbers automatically when user hits submit on a form using:

.Cells(emptyRow, 2) = Format(Int(Left(.Cells(emptyRow - 1, 2).Value, 3)) + 1, "000") & "/" & current_year

where 

emptyRow = WorksheetFunction.CountA(nwb.Sheets("Sheet1").Range("A:A")) + 1 'To calculate next empty row

current_year = FinancialYear(Date)

The full code is(I haven't included other unnecessary stuff):

Function FinancialYear(dDate As Date) As Integer
  If Month(dDate) <= 3 Then
    FinancialYear = Year(dDate) - 1
  Else
    FinancialYear = Year(dDate)
  End If
End Function

Sub Submit()
    Dim nwb As Workbook
    Set nwb = Workbooks.Open("C:\Users\CHAMARA2\Downloads\TestDB.xlsx")

    Dim emptyRow As Long

    Dim current_year As String
    emptyRow = WorksheetFunction.CountA(nwb.Sheets("Sheet1").Range("A:A")) + 1

    With nwb.Sheets("Sheet1")

        .Cells(emptyRow, 1) = emptyRow - 1  'serial number

        'If current_year = 2020 Or current_year = 2022 Or current_year = 2023 Or current_year = 2024 Or current_year = 2025 Then .Cells(emptyRow, 2) = "000/" & current_year

        .Cells(emptyRow, 2) = Format(Int(Left(.Cells(emptyRow - 1, 2).Value, 3)) + 1, "000") & "/" & current_year

    End With
End Sub

Upvotes: 1

Views: 815

Answers (2)

HTH
HTH

Reputation: 2031

you could check previous row invoice number last 4 digits (for instance with IIF() function):

Dim nwb As Workbook
Set nwb = Workbooks.Open("C:\Users\CHAMARA2\Downloads\TestDB.xlsx")

Dim current_year As String
current_year = FinancialYear(Date)

With nwb.Sheets("Sheet1")
    With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
        .Value = .Row - 1 'serial number
        .Offset(, 1) = Format(Int(Left$(IIf(Right$(.Offset(-1, 1).Value, 4) = current_year, .Offset(-1, 1).Value, 0), 3)) + 1, "000") & "/" & current_year
    End With
End With

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

Right before generating your incremented invoice number just check if invoice number 1 of the current year 001/2021 already exists in your invoices. Eg by using the WorksheetFunction.Match method.

If that invoice number exists, then the invoice counter is already in the current year, if it does not exist we need to reset the counter to 0 because no invoice was written in the current year yet.

Dim FirstInvoice As String
FirstInvoice = "001" & "/" & current_year

Dim MatchInvoice As Double
On Error Resume Next 'next line will error if invoice 001/2021 does not exist yet
MatchInvoice = Applicarion.WorksheetFunction.Match(FirstInvoice, RangeOfInvoceNumbers, 0)
On Error Goto 0  're-enable error reporting

If MatchInvoice = 0 Then
    'first invoice of the current year does not exist so
    'reset your invoice counter to 0 here
End If

'proceed generating your increment as you did before.

This will not reset the counter directly in 1. January but whenever the first invoice is generated in the new year. So this even works if you write your first invoice in June for example.

A bit off topic:

Note that using this format 001/2021 will sort like this:

001/2020
001/2021
001/2022
002/2020
002/2022
002/2023

So you might consider a better format like:

2020/001
2020/002
2021/001
2021/002
2022/001
2022/002

that will automatically sort year wise.

If you keep the actual stored invoice number numeric like 2020001 and just use the number format 0000"/"000 to show it like 2020/001 for better human readability, you can even use the MAX function to find the latest invoice number in your invoices and increment that by calculating +1.

Then you could also use Application.WorksheetFunction.Max to check if you need to reset and increment more easily

Dim FirstInvoiceCurrentYear As Long
FirstInvoiceCurrentYear = cLng(current_year & "001")

Dim CurrentInvoice As Long
CurrentInvoice = Application.WorksheetFunction.Max(RangeOfInvoceNumbers)

Dim NextInvoiceNumber As Long
If FirstInvoiceCurrentYear > CurrentInvoice Then
     NextInvoiceNumber = FirstInvoiceCurrentYear 
Else
     NextInvoiceNumber = CurrentInvoice + 1
End If

So you see choosing a better format makes your life much easier in some points.

You just need to ensure that any cell containing the invoice number is set to number format 0000"/"000 if you then enter 2020001 into that cell it will automaticall show up as 2020/001, but the cell is still a number you can calculate with.

Upvotes: 1

Related Questions