用公式自动填充列：双向计算单价与总价的VBA报错求助

原代码问题分析 #

你碰到的Runtime Error 13（类型不匹配）主要有两个诱因：

• 当选中多个单元格修改时，Target是多单元格区域，直接用Target = ""做对比会触发类型不匹配——VBA没法把区域对象和空字符串直接做判断。
• 修改单元格会触发Worksheet_Change事件，写入公式又会再次触发该事件，容易引发死循环或异常。

完整解决方案代码 #

下面的代码实现数量×单价=总价的双向计算：输入单价自动算出总价，输入总价自动算出单价，清空单价/总价时恢复公式关联逻辑。假设你已经给三列设置了命名区域：

• 数量列：Qty
• 单价列：UnitPrice
• 总价列：TotalPrice

如果没设置命名区域，直接把代码里的命名区域替换成单元格范围（比如Range("B:B")代表单价列）即可。

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 关闭事件触发，避免写入公式时重复触发Change事件
    Application.EnableEvents = False
    
    Dim cell As Range
    ' 只处理单价列或总价列的单元格变化
    For Each cell In Intersect(Target, Union(Range("UnitPrice"), Range("TotalPrice")))
        On Error Resume Next ' 避免除数为0等计算错误
        
        ' 如果修改的是单价列
        If Not Intersect(cell, Range("UnitPrice")) Is Nothing Then
            ' 若单价不为空，计算对应行的总价
            If cell.Value <> "" Then
                cell.Offset(0, 1).Value = cell.Value * cell.Offset(0, -1).Value ' 假设总价在单价右侧一列，数量在左侧一列
            Else
                ' 清空单价时，恢复总价的公式（数量×单价）
                cell.Offset(0, 1).Formula = "=" & cell.Offset(0, -1).Address & "*" & cell.Address
            End If
        ' 如果修改的是总价列
        ElseIf Not Intersect(cell, Range("TotalPrice")) Is Nothing Then
            ' 若总价不为空，计算对应行的单价（总价/数量）
            If cell.Value <> "" And cell.Offset(0, -2).Value <> 0 Then ' 数量不能为0
                cell.Offset(0, -1).Value = cell.Value / cell.Offset(0, -2).Value
            Else
                ' 清空总价时，恢复单价的公式（总价/数量）
                cell.Offset(0, -1).Formula = "=" & cell.Address & "/" & cell.Offset(0, -2).Address
            End If
        End If
        
        On Error GoTo 0 ' 恢复错误捕获
    Next cell
    
    ' 重新开启事件触发
    Application.EnableEvents = True
End Sub

代码说明 #

• Application.EnableEvents = False：修改单元格前关闭事件触发，防止写入公式时重复触发Worksheet_Change导致循环。
• 循环Target中的每个单元格：处理用户修改多个单元格的情况，从根源避免类型不匹配问题。
• 双向计算逻辑：修改单价时自动计算对应总价，修改总价时自动计算对应单价；清空值时恢复公式关联。
• 错误处理：加入On Error Resume Next避免数量为0时的除法错误。

注意事项 #

1. 确保数量列的值不为0，否则计算单价会出错。
2. 如果你的列顺序不是「数量→单价→总价」，需要调整Offset的参数（比如Offset(0, -1)是左侧一列，Offset(0,1)是右侧一列）。
3. 测试前记得把Excel文件保存为.xlsm格式（启用宏的工作簿），否则代码无法运行。

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