如何通过VBA将inItemDetail表采购数量更新至Product表库存

问题背景 #

现有两个工作表：

• Product表：存储产品基础数据，包含序号、Product ID、产品分类、产品名称、单位、Stock、仓库7列
• inItemDetail表：存储采购明细数据，包含Trans ID、发票号、日期、供应商、仓库、Product ID、产品名称、产品分类、QTY、单位10列

已实现点击保存按钮时将列表框数据写入inItemDetail表，但无法批量按Product ID累加更新Product表的Stock字段（目前仅支持单条更新）。

修改后的完整代码 #

Private Sub saveTransButton_Click()
    Dim DBDetailTransaction As Range
    Dim DetailData As Long
    Dim wsProduct As Worksheet
    Dim wsInItem As Worksheet
    Dim lastRowProduct As Long
    Dim matchRow As Variant
    Dim currentQty As Double
    Dim currentProductID As String
    
    ' 定义工作表对象，避免模糊引用
    Set wsProduct = ThisWorkbook.Worksheets("Product")
    Set wsInItem = ThisWorkbook.Worksheets("inItemDetail")
    Set DBDetailTransaction = Sheet10.Range("A80000").End(xlUp)

    ' 校验必填字段
    If Me.transcationIDInputBox.Value = "" _
    Or Me.invoiceNumberInputBox.Value = "" _
    Or Me.dateTransactionInputBox.Value = "" _
    Or Me.dropDownSupplierTranscation.Value = "" _
    Or Me.dropdDownWarehouseTransaction.Value = "" Then

        MsgBox "请填写完整的交易明细和商品信息字段", vbInformation, "交易数据输入提示"
        
    Else
        ' 写入交易主数据到Sheet10
        DBDetailTransaction.Offset(1, 0).Value = "=ROW()-ROW($A$3)"
        DBDetailTransaction.Offset(1, 1).Value = transcationIDInputBox.Value
        DBDetailTransaction.Offset(1, 2).Value = invoiceNumberInputBox.Value
        DBDetailTransaction.Offset(1, 3).Value = dateTransactionInputBox.Value
        DBDetailTransaction.Offset(1, 4).Value = dropDownSupplierTranscation.Value
        DBDetailTransaction.Offset(1, 5).Value = dropdDownWarehouseTransaction.Value
        DBDetailTransaction.Offset(1, 6).Value = totalItemsTransInputBox.Value
        
        ' 写入列表框数据到inItemDetail表，并批量更新库存
        For DetailData = 1 To Me.itemTransactionDataTable.ListCount - 1
            ' 获取当前行的Product ID和采购数量（列表框索引从0开始）
            currentProductID = Me.itemTransactionDataTable.List(DetailData, 5)
            currentQty = Me.itemTransactionDataTable.List(DetailData, 8)
            
            ' 写入数据到inItemDetail表
            With wsInItem.Range("A" & wsInItem.Rows.Count).End(xlUp).Offset(1, 0)
                .Value = Me.itemTransactionDataTable.List(DetailData, 0)
                .Offset(0, 1).Value = Me.itemTransactionDataTable.List(DetailData, 1)
                .Offset(0, 2).Value = Me.itemTransactionDataTable.List(DetailData, 2)
                .Offset(0, 3).Value = Me.itemTransactionDataTable.List(DetailData, 3)
                .Offset(0, 4).Value = Me.itemTransactionDataTable.List(DetailData, 4)
                .Offset(0, 5).Value = currentProductID
                .Offset(0, 6).Value = Me.itemTransactionDataTable.List(DetailData, 6)
                .Offset(0, 7).Value = Me.itemTransactionDataTable.List(DetailData, 7)
                .Offset(0, 8).Value = currentQty
                .Offset(0, 9).Value = Me.itemTransactionDataTable.List(DetailData, 9)
            End With
            
            ' 批量更新Product表的Stock
            lastRowProduct = wsProduct.Range("B" & wsProduct.Rows.Count).End(xlUp).Row ' B列对应Product ID
            ' 查找匹配的Product ID行
            matchRow = Application.Match(currentProductID, wsProduct.Range("B2:B" & lastRowProduct), 0)
            
            If Not IsError(matchRow) Then
                ' 找到匹配项，累加库存（F列对应Stock）
                wsProduct.Range("F" & matchRow + 1).Value = wsProduct.Range("F" & matchRow + 1).Value + currentQty
            Else
                ' 未找到匹配项，弹出提示
                MsgBox "未找到Product ID为 " & currentProductID & " 的产品，库存未更新", vbExclamation, "库存更新提示"
            End If
        Next DetailData
        
        ' 清空表单控件
        Me.transcationIDInputBox = ""
        Me.invoiceNumberInputBox = ""
        Me.dateTransactionInputBox = ""
        Me.dropDownSupplierTranscation = ""
        Me.dropdDownWarehouseTransaction = ""
        Me.itemTransactionDataTable.Clear
        
    End If
    Unload detailInItemForm
End Sub

关键改进点说明 #

• 明确工作表引用：用ThisWorkbook.Worksheets("Product")替代模糊的Sheet5/Sheet10，避免因工作表顺序变化导致错误
• 批量处理逻辑：在写入每条采购明细的同时，通过Application.Match快速定位对应Product ID的行，直接累加Stock值
• 简化代码结构：使用With语句减少重复代码，提升运行效率和可读性
• 错误提示机制：对未匹配到的Product ID给出明确提示，方便排查数据问题

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