VBA中XLookup无法返回匹配值的问题求助

问题背景 #

• 制作了仅用于数据展示的UserForm，需与同工作簿内MARKETING_FORM工作表的数据对比
• 最终目标：当UserForm中部件号对应的颜色与工作表中对应颜色不匹配时，将UserForm上的颜色文本设为红色加粗；当前先通过消息框获取匹配结果
• 核心元素对应关系：
  • TBITEMNUMBER.Value：UserForm上存储部件号的文本框（示例值：11100345）
  • MARKETING_FORM!E:E：目标工作表中存储部件号的列
  • MARKETING_FORM!H:H：目标工作表中对应部件号的颜色名称列（示例值：SILVER）

现有问题代码 #

Sub CommandButton3_Click()

Dim Lookup_Value As String
Dim LookupA As Range
Dim ReturnA As Range
Dim If_Not_Found As String
Dim Result As Variant

Lookup_Value = TBITEMNUMBER.Value
Set LookupA = Workbook.Sheets("MARKETING_FORM").Range("E:E")
Set ReturnA = Workbook.Sheets("MARKETING_FORM").Range("H:H")
If_Not_Found = "Item Number Not Found"

Result = Application.WorksheetFunction.Xlookup(Lookup_Value, LookupA, ReturnA, If_Not_Found)

MsgBox Result

End Sub

问题现象：确认部件号存在于目标工作表，但消息框始终显示Item Number Not Found

问题排查与修正方案 #

核心错误点 #

1. 工作簿对象引用错误：Workbook.Sheets未指定当前工作簿，需改为ThisWorkbook.Sheets，否则可能引用其他打开的工作簿
2. 数据类型/格式不匹配：若工作表E列的部件号是数字格式，而Lookup_Value是字符串类型，会导致精确匹配失败；另外输入值或工作表单元格存在前后空格，也会破坏匹配
3. 错误处理机制问题：WorksheetFunction.Xlookup在匹配失败时会直接抛出运行时错误，不如Application.Xlookup灵活（可返回自定义提示）

修正后的基础验证代码 #

Sub CommandButton3_Click()
    Dim Lookup_Value As String
    Dim ws As Worksheet
    Dim Result As Variant
    
    ' 指定当前工作簿的目标工作表
    Set ws = ThisWorkbook.Sheets("MARKETING_FORM")
    ' 去除输入值前后空格，避免空格导致匹配失败
    Lookup_Value = Trim(TBITEMNUMBER.Value)
    
    ' 使用Application.Xlookup，匹配失败时返回自定义提示而非报错
    Result = Application.Xlookup(Lookup_Value, ws.Range("E:E"), ws.Range("H:H"), "Item Number Not Found")
    
    MsgBox Result
End Sub

最终需求实现（颜色不匹配时设置UserForm文本格式） #

假设UserForm上显示颜色的控件为TBCOLOR（请替换为你实际使用的控件名称），代码如下：

Sub CommandButton3_Click()
    Dim Lookup_Value As String
    Dim ws As Worksheet
    Dim SheetColor As Variant
    Dim UserFormColor As String
    
    Set ws = ThisWorkbook.Sheets("MARKETING_FORM")
    Lookup_Value = Trim(TBITEMNUMBER.Value)
    UserFormColor = Trim(TBCOLOR.Value) ' 替换为你显示颜色的控件名
    
    ' 获取工作表中对应部件号的颜色
    SheetColor = Application.Xlookup(Lookup_Value, ws.Range("E:E"), ws.Range("H:H"), "")
    
    If SheetColor <> "" Then
        ' 统一转为大写对比，避免大小写差异导致误判
        If UCase(UserFormColor) <> UCase(SheetColor) Then
            With TBCOLOR ' 替换为实际控件名
                .ForeColor = vbRed
                .Font.Bold = True
            End With
        Else
            ' 匹配时恢复默认格式
            With TBCOLOR
                .ForeColor = vbBlack
                .Font.Bold = False
            End With
        End If
    Else
        MsgBox "Item Number Not Found"
    End If
End Sub

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