如何用VBA生成含客户编码的无重复连续发票编号？

需求说明 #

• 单元格C2为客户编码，每个客户的发票序列号需独立连续递增
• 单元格C4生成的发票编号格式为JS-12-22-10-001，各段含义：
  • JS：固定公司简称
  • 12：当前月份（两位）
  • 22：当前年份后两位
  • 10：客户编码（取自C2）
  • 001：该客户的连续序列号（3位补零）
• 现有测试代码仅实现简单数值递增，无法区分客户，也无持久化存储，易出现重复或断号问题

最优解决方案：用单独工作表存储历史记录 #

必须使用独立工作表保存发票历史数据，这是保证序列号连续、无重复的核心——只有持久化记录每个客户的已用序列号，才能避免因文件关闭、数据修改导致的断号或重复。

操作步骤 #

1. 在当前工作簿中新建工作表，命名为发票记录
2. 在发票记录表的A1:C1分别输入表头：客户编码、发票编号、生成日期
3. 替换现有测试代码为以下完整VBA代码

完整VBA代码 #

Sub GenerateInvoiceNumber()
    Dim wsMain As Worksheet, wsRecord As Worksheet
    Dim customerCode As String, maxSeq As Long
    Dim yearPart As String, monthPart As String
    Dim newSeq As String, invoiceNumber As String
    
    ' 指定主工作表和记录工作表
    Set wsMain = ThisWorkbook.Sheets("Sheet1") ' 可根据实际表名修改
    Set wsRecord = ThisWorkbook.Sheets("发票记录")
    
    ' 获取客户编码（C2），空值则退出
    customerCode = Trim(wsMain.Range("C2").Value)
    If customerCode = "" Then
        MsgBox "请先输入客户编码！", vbExclamation
        Exit Sub
    End If
    
    ' 获取年月段（两位月份+两位年份）
    monthPart = Format(Date, "mm")
    yearPart = Format(Date, "yy")
    
    ' 查询当前客户的最大已用序列号
    maxSeq = 0
    Dim lastRow As Long, cell As Range
    lastRow = wsRecord.Cells(wsRecord.Rows.Count, "A").End(xlUp).Row
    For Each cell In wsRecord.Range("A2:A" & lastRow)
        If cell.Value = customerCode Then
            ' 提取发票编号最后一段的序列号并转为数字
            Dim tempSeq As Long
            tempSeq = CLng(Split(cell.Offset(0, 1).Value, "-")(4))
            If tempSeq > maxSeq Then maxSeq = tempSeq
        End If
    Next cell
    
    ' 生成新序列号，补零为3位格式
    newSeq = Format(maxSeq + 1, "000")
    
    ' 组合生成完整发票编号
    invoiceNumber = "JS-" & monthPart & "-" & yearPart & "-" & customerCode & "-" & newSeq
    
    ' 写入主表C4单元格
    wsMain.Range("C4").Value = invoiceNumber
    
    ' 将新记录保存到发票记录表
    lastRow = wsRecord.Cells(wsRecord.Rows.Count, "A").End(xlUp).Row + 1
    wsRecord.Cells(lastRow, "A").Value = customerCode
    wsRecord.Cells(lastRow, "B").Value = invoiceNumber
    wsRecord.Cells(lastRow, "C").Value = Date
    
    MsgBox "发票编号已生成：" & invoiceNumber, vbInformation
End Sub

代码说明 #

1. 工作表指定：明确主表和记录工作表，可根据实际表名修改
2. 客户编码校验：确保C2不为空，避免无效生成
3. 年月段自动获取：基于系统日期生成两位月份和年份，无需手动输入
4. 序列号连续性保障：遍历历史记录，找出当前客户的最大已用序列号，新序列号在此基础上加1
5. 格式统一：序列号自动补零为3位，保证发票编号格式规范
6. 持久化存储：生成的编号同步保存到发票记录表，可随时追溯历史数据

额外注意事项 #

• 可保护发票记录表避免误修改：右键工作表 → 保护工作表，设置密码
• 若需修改公司简称（JS），直接替换代码中"JS-"部分即可
• 客户编码为数字类型时，代码无需修改，自动兼容

内容的提问来源于stack exchange，提问作者roy