编写VBA宏实现tCotizacion表格UnitPrice按比例分摊nCostLogTot

现有Invoice_InsertProduct宏可向Sheet1的tCotizacion表格插入行，以下是新增的Prorate_UnitPrice宏代码，绑定按钮后可将Y9单元格的nCostLogTot值按比例分摊至表格的UnitPrice列，替换每行原有单价。

分摊逻辑 #

公式规则：

新UnitPrice = 原有UnitPrice + IF(ISNUMBER(nCostLogT), ((tCotizacion[ProductAmount]/nTotalBalance)*nCostLogT)/tCotizacion[ProductQuantity], 0)

• nCostLogT：取Y9单元格的数值，非数值则不执行分摊
• nTotalBalance：表格所有行ProductAmount列的总和
• 遍历tCotizacion表格每一行完成计算

完整VBA代码 #

Sub Prorate_UnitPrice()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim nCostLogT As Double
    Dim nTotalBalance As Double
    Dim row As ListRow
    
    ' 绑定目标工作表和表格
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tbl = ws.ListObjects("tCotizacion")
    
    ' 获取分摊总额，非数值则设为0
    nCostLogT = IIf(IsNumeric(ws.Range("Y9").Value), ws.Range("Y9").Value, 0)
    
    ' 计算ProductAmount列的总和，处理表格无数据的情况
    On Error Resume Next
    nTotalBalance = Application.WorksheetFunction.Sum(tbl.ListColumns("ProductAmount").DataBodyRange)
    On Error GoTo 0
    
    ' 避免除以0，仅当总和和分摊额都不为0时执行
    If nTotalBalance > 0 And nCostLogT <> 0 Then
        ' 遍历所有数据行
        For Each row In tbl.ListRows
            Dim originalPrice As Double
            Dim productAmt As Double
            Dim productQty As Double
            Dim priceAddOn As Double
            
            originalPrice = row.Range(tbl.ListColumns("UnitPrice").Index).Value
            productAmt = row.Range(tbl.ListColumns("ProductAmount").Index).Value
            productQty = row.Range(tbl.ListColumns("ProductQuantity").Index).Value
            
            ' 计算分摊的单价增量
            priceAddOn = ((productAmt / nTotalBalance) * nCostLogT) / productQty
            ' 更新UnitPrice
            row.Range(tbl.ListColumns("UnitPrice").Index).Value = originalPrice + priceAddOn
        Next row
    End If
    
    MsgBox "单价分摊操作完成", vbInformation
End Sub

关键细节说明 #

• 用ListObject操作表格，避免硬编码列号，表格列顺序调整后代码依然可用
• 加入错误处理，防止表格无数据时Sum函数报错
• 增加除以0的判断，避免运行时错误
• 遍历用For Each row In tbl.ListRows，比索引遍历更直观

示例验证 #

当nCostLogT为$600时，按示例数据计算后，两行的UnitPrice会分别更新为$21.5和$32.25（对应示例中的分摊比例）。

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