请求编写VBA宏实现Sheet列值匹配替换及排查运行时错误13

错误原因 #

你遇到的「Run-time error 13: Type mismatch」主要由以下几个问题导致：

1. 字符串拼接错误：VBA中字符串与数字拼接必须用&，而非+。"B" + i会因类型不匹配触发错误，因为+是算术运算符，无法直接将字符串和数字相加。
2. Match返回值理解偏差：WorksheetFunction.Match返回的是查找范围内的相对行号（比如你查找的是B2:B3000，返回1对应Sheet2的B2行），不是绝对行号，直接用matchIndex作为行号会指向错误的行。
3. 错误判断逻辑失效：matchIndex是Integer类型，当Match未找到匹配项时，On Error Resume Next会让matchIndex保留之前的值，导致IsNA(matchIndex)判断完全无效，可能执行错误的替换操作。

修正后的代码 #

Dim wb As Workbook, firstWs As Worksheet, secondWs As Worksheet
Dim matchResult As Variant ' 改用Variant存储Match结果，因为可能返回错误值

Set wb = ThisWorkbook
Set firstWs = wb.Worksheets(1)
Set secondWs = wb.Worksheets(2)

Application.ScreenUpdating = False

' 遍历Sheet1的B列，从第2行到数据最后一行（避免固定行数限制）
For i = 2 To firstWs.Cells(firstWs.Rows.Count, "B").End(xlUp).Row
    ' 用Application.Match替代WorksheetFunction.Match，未找到时返回错误值而非抛出错误
    matchResult = Application.Match(firstWs.Range("B" & i).Value, secondWs.Range("B2:B3000"), 0)
    
    ' 判断是否找到匹配项
    If Not IsError(matchResult) Then
        ' Match返回的是相对行号，加上查找范围的起始行偏移（B2对应行2，所以+1）
        firstWs.Range("B" & i).Value = secondWs.Range("C" & (matchResult + 1)).Value
    End If
Next i

Application.ScreenUpdating = True

关键修改说明 #

• 将matchIndex改为Variant类型，因为Application.Match未找到匹配时会返回Error 2042，只有Variant能存储错误值。
• 用IsError(matchResult)直接判断是否找到匹配，无需错误捕获，逻辑更清晰。
• 字符串拼接全部改用&，彻底避免类型不匹配问题。
• 计算Sheet2的目标行时，用matchResult + 1转换为绝对行号（因为查找范围从B2开始，相对行号1对应绝对行2）。
• 遍历范围改为动态获取Sheet1 B列最后一行，避免固定行数（A2:A2000）导致的遗漏或多余循环。

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