使用XLOOKUP结合TEXT函数时出现错误13类型不匹配问题求助

错误原因 #

你代码里的WorksheetFunction.Text不能直接作用于多单元格区域（比如Sheets("tblItem").Columns(2)整列），这个函数在VBA中仅支持单个单元格或单个值作为输入，传入整列会直接触发错误13（类型不匹配）——这也是和工作表公式中TEXT函数的核心区别：工作表公式会自动处理数组区域，但VBA的WorksheetFunction版本不支持。

解决方案 #

方案1：统一转换为数值类型匹配 #

如果你的匹配值都是数字（不管是文本存储还是数值存储），直接转成数值后匹配最简洁：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅处理单个单元格修改，避免批量操作出错
    If Target.Cells.Count > 1 Then Exit Sub
    
    Dim lookupVal As Variant
    Dim lookupRng As Range
    Dim returnRng As Range
    
    Set lookupRng = Sheets("tblItem").Columns(2)
    Set returnRng = Sheets("tblItem").Columns(3)
    
    ' 将Target的值转为数值（文本型数字会自动转换）
    lookupVal = Val(Target.Value)
    
    ' 使用Application.XLookup（而非WorksheetFunction），出错时返回指定提示
    Target.Offset(0, 1).Value = Application.XLookup(lookupVal, lookupRng.Value, returnRng.Value, "无匹配结果")
End Sub

方案2：统一转换为文本类型匹配 #

如果需要保留文本格式，或者匹配值包含非数字内容，可将所有匹配项转成文本数组后查找：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    
    Dim lookupVal As String
    Dim lookupArr As Variant
    Dim returnArr As Variant
    Dim tblSheet As Worksheet
    
    Set tblSheet = Sheets("tblItem")
    
    ' 将Target的值转为文本
    lookupVal = CStr(Target.Value)
    
    ' 用Evaluate批量将整列转为文本数组
    lookupArr = Application.Evaluate("TEXT(" & tblSheet.Columns(2).Address(External:=True) & ",""General"")")
    returnArr = Application.Evaluate("TEXT(" & tblSheet.Columns(3).Address(External:=True) & ",""General"")")
    
    ' 执行文本匹配
    Target.Offset(0, 1).Value = Application.XLookup(lookupVal, lookupArr, returnArr, "无匹配结果")
End Sub

注意事项 #

• 优先用Application.XLookup而非WorksheetFunction.XLookup：前者在匹配失败时不会直接抛出运行时错误，而是返回错误值或你指定的默认值，更适合VBA容错。
• 加上Target.Cells.Count > 1的判断：避免用户批量修改单元格时代码崩溃。

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