求助：VBA运行时错误'13'（类型不匹配）问题排查

Hey there! As a fellow VBA learner, I totally get how frustrating that "Type mismatch" error can be. Let's walk through your code and figure out where things are going wrong, plus fix them up step by step.

你的原始问题与代码 #

本人是VBA新手，仍在学习编程语言与编码。参考多篇在线教程和帖子后编写了以下项目代码，但运行时出现“Run-time Error: Type mismatch”错误。恳请各位专家帮忙排查错误，万分感谢！

代码如下：

Dim wsServiceRegistry As Worksheet, wsInhouseMaterialInventory As Worksheet
Dim updateCell As Range
Dim emptyRow As Long
With ThisWorkbook
    Set wsServiceRegistry = .Worksheets("Service Registry")
    Set wsInhouseMaterialInventory = .Worksheets("Inhouse Material Inventory")
End With
Set updateCell = wsInhouseMaterialInventory.Cells(Me.InhouseMaterialComboBox.ListIndex + 2, 4)
updateCell.Value = updateCell.Value - Val(Me.MaterialQuantityTextBox.Value)
If updateCell.Value < 1 Then updateCell.EntireRow.Delete: Exit Sub

可能的错误原因及修复方法 #

Type mismatch errors usually pop up when you try to perform operations on mismatched data types (like subtracting a number from text). Here are the most likely culprits in your code:

1. 未选中ComboBox选项导致无效索引

When the InhouseMaterialComboBox has no selected item, its ListIndex returns -1. Adding 2 to that gives you 1, which points to cell D1 in your inventory sheet. If that cell contains text instead of a number, subtracting a value from it will trigger the error. Even if it's a number, this is probably not the row you intended to update.

Fix: Add a check to ensure an item is selected before proceeding:

' 先检查ComboBox是否选中了项
If Me.InhouseMaterialComboBox.ListIndex = -1 Then
    MsgBox "请先选择要操作的内部物料！"
    Exit Sub
End If

2. 物料数量输入无效

If the MaterialQuantityTextBox is empty, or contains non-numeric characters (like letters, symbols), Val() will return 0—but if the target cell's value is text, subtracting a number from it will still cause a type mismatch. Even if the target cell is numeric, invalid input can lead to unexpected behavior.

Fix: Validate the input first and convert it to a proper numeric type:

Dim quantity As Double
' 检查输入是否为有效数字
If Not IsNumeric(Me.MaterialQuantityTextBox.Value) Then
    MsgBox "请输入有效的物料数量（只能是数字）！"
    Me.MaterialQuantityTextBox.SetFocus ' 让光标回到输入框方便修改
    Exit Sub
End If
quantity = CDbl(Me.MaterialQuantityTextBox.Value) ' 转换为双精度数字

3. 目标单元格内容不是数值类型

If the cell you're trying to update (updateCell) contains text (e.g., manual text entries, formula results that are text), subtracting a number from it will immediately throw a type mismatch error.

Fix: Add a check to confirm the cell holds a valid number:

' 检查目标单元格是否为有效数值
If Not IsNumeric(updateCell.Value) Then
    MsgBox "无法更新：目标单元格的内容不是有效数值！"
    Exit Sub
End If

修复后的完整代码 #

Here's your code with all the fixes included, plus some extra readability tweaks:

Dim wsServiceRegistry As Worksheet, wsInhouseMaterialInventory As Worksheet
Dim updateCell As Range
Dim quantity As Double

With ThisWorkbook
    Set wsServiceRegistry = .Worksheets("Service Registry")
    Set wsInhouseMaterialInventory = .Worksheets("Inhouse Material Inventory")
End With

' 检查ComboBox是否选中项
If Me.InhouseMaterialComboBox.ListIndex = -1 Then
    MsgBox "请先选择要操作的内部物料！"
    Exit Sub
End If

' 检查并转换数量输入
If Not IsNumeric(Me.MaterialQuantityTextBox.Value) Then
    MsgBox "请输入有效的物料数量（只能是数字）！"
    Me.MaterialQuantityTextBox.SetFocus
    Exit Sub
End If
quantity = CDbl(Me.MaterialQuantityTextBox.Value)

' 设置目标单元格
Set updateCell = wsInhouseMaterialInventory.Cells(Me.InhouseMaterialComboBox.ListIndex + 2, 4)

' 检查目标单元格是否为数值
If Not IsNumeric(updateCell.Value) Then
    MsgBox "无法更新：目标单元格的内容不是有效数值！"
    Exit Sub
End If

' 执行减法操作
updateCell.Value = updateCell.Value - quantity

' 删除数量小于1的行
If updateCell.Value < 1 Then
    updateCell.EntireRow.Delete
    Exit Sub
End If

额外小贴士 #

• Always add input validation for user controls (like ComboBoxes and TextBoxes)—it prevents most runtime errors and makes your code more user-friendly.
• Use explicit data types (like Double instead of relying on Val()) to avoid unexpected type conversions.

内容的提问来源于stack exchange，提问作者T.Sarathchandra