在Excel中的新财政年度开始时，将自动发票生成重置为1000/“当前年度”

Question

我有一个工作代码，当用户在用户表单中输入其他细节并点击提交时，将自动以112/2020、113/2020等格式递增发票号(用于2020财政年度)。

我想要的是，根据我的财政年度职能，在4月份的财政年度发生变化时，尽快将此发票号重新设置为001/2021。而且，当财政年度增加时，政府每年都应该这样做。

我所使用的计算当前财政年度的函数如下

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

当用户点击在表单上提交时，我将自动增加发票号，使用：

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

哪里

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

current_year = FinancialYear(Date)

完整的代码是(我没有包括其他不必要的内容)：

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

Answer 1

在生成递增的发票号之前，只需检查当前年度的发票号1是否已经存在于您的发票中。例如使用WorksheetFunction.Match法。

如果该发票号存在，那么发票计数器已经在当前年份了，如果它不存在，我们需要将计数器重置为0，因为在当前年份还没有写发票。

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.

这将不会在1月1日直接重置计数器，但只要在新的一年中产生第一张发票。例如，如果你在六月开了第一张发票，这甚至是可行的。

有点离题：

请注意，使用此格式的001/2021将排序如下：

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

因此，您可能会考虑一种更好的格式，如：

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

这将自动对年份进行排序。

如果您将实际存储的发票编号保持为2020001，并且只使用数字格式0000"/"000来显示它类似于2020/001，那么您甚至可以使用MAX函数在您的发票中查找最新的发票号，并通过计算+1来增加它。

然后，您还可以使用Application.WorksheetFunction.Max检查是否需要更容易地重置和增量。

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

因此，你可以看到，选择一种更好的格式会使你的生活在某些方面变得容易得多。

您只需要确保任何包含发票号的单元格都设置为number格式的0000"/"000，如果然后在该单元格中输入2020001，它将自动调用显示为2020/001，但单元格仍然是可以计算的数字。

Answer 2

您可以检查前一行发票号最后4位(例如，使用IIF()函数)：

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