为何VBA中VLookup在一台电脑返回错误2042，另一台正常？

问题场景 #

VBA脚本在编写电脑上可正常运行，但在另一台电脑上未做任何修改，执行时VLookup返回错误2042。脚本核心需求：

• 用当日日期填充指定列
• 将日期存入变量
• 通过VLookup查找该日期对应的周数并填入相邻列

原代码 #

Sub macro1()

    Dim source As Range
    Dim theval As Date
       
    'Source is a calendar table where the value being looked for is in col. C and its corresponding week in col. H         
    Set source = Workbooks("Catalogo Global.xlsx").Sheets(3).Range("C2:H731")
    
    'Populate entire column with today's date:
    ThisWorkbook.Sheets(1).Range("Q2:Q10").Formula = "=TODAY()"
    

    theval = ThisWorkbook.Sheets(1).Range("Q2").Value
    
    '*** HERE IS THE PROBLEM:Adding .value after "source" was what made the original code work but is now returning error 2042 in the current computer
    LookedValue = Application.VLookup(theval, source.Value, 6, False)

End Sub

关键注意点 #

原代码中在source后添加.Value曾让编写电脑正常运行，但在新电脑执行时返回错误2042；移除.Value后问题依旧。

已完成的排查 #

• 验证source区域非空
• 确认查找值在源表中存在，且两者数据类型均为日期（类型值7）
• 在工作表中手动执行VLookup可正常匹配，无错误

可行的解决方向 #

1. 统一日期为序列号匹配 #

不同电脑的系统日期区域设置（如MM/DD/YYYY vs DD/MM/YYYY）可能导致日期匹配异常，将日期转换为Excel内部序列号（长整数）可规避该问题：

Sub macro1()

    Dim source As Range
    Dim theval As Long
       
    Set source = Workbooks("Catalogo Global.xlsx").Sheets(3).Range("C2:H731")
    
    ThisWorkbook.Sheets(1).Range("Q2:Q10").Formula = "=TODAY()"
    
    ' 将日期转换为序列号
    theval = CLng(ThisWorkbook.Sheets(1).Range("Q2").Value)
    
    ' 源区域也转换为序列号数组进行匹配
    LookedValue = Application.VLookup(theval, CLng(source.Value), 6, False)

End Sub

2. 确保源工作簿引用可靠 #

直接传递Range对象给VLookup，同时先确认源工作簿已正确打开（避免因路径或打开状态导致的引用问题）：

Sub macro1()

    Dim source As Range
    Dim theval As Date
    Dim wbCatalog As Workbook
       
    ' 先检查工作簿是否已打开，未打开则用完整路径打开
    On Error Resume Next
    Set wbCatalog = Workbooks("Catalogo Global.xlsx")
    On Error GoTo 0
    If wbCatalog Is Nothing Then
        ' 替换为实际文件路径
        Set wbCatalog = Workbooks.Open("D:\文件路径\Catalogo Global.xlsx")
    End If
    
    Set source = wbCatalog.Sheets(3).Range("C2:H731")
    
    ThisWorkbook.Sheets(1).Range("Q2:Q10").Formula = "=TODAY()"
    
    theval = ThisWorkbook.Sheets(1).Range("Q2").Value
    
    ' 直接传递Range对象
    LookedValue = Application.VLookup(theval, source, 6, False)

End Sub

3. 使用WorksheetFunction捕获错误 #

WorksheetFunction.VLookup匹配失败时会抛出明确错误，可通过错误捕获定位问题：

Sub macro1()

    Dim source As Range
    Dim theval As Date
       
    Set source = Workbooks("Catalogo Global.xlsx").Sheets(3).Range("C2:H731")
    
    ThisWorkbook.Sheets(1).Range("Q2:Q10").Formula = "=TODAY()"
    
    theval = ThisWorkbook.Sheets(1).Range("Q2").Value
    
    On Error Resume Next
    LookedValue = WorksheetFunction.VLookup(theval, source, 6, False)
    If Err.Number <> 0 Then
        ' 输出调试信息到立即窗口
        Debug.Print "匹配失败：查找值为 " & theval
        Debug.Print "源表第一行日期：" & source.Cells(1, 1).Value
    End If
    On Error GoTo 0

End Sub

4. 检查源表状态 #

确认源表的C列（查找列）和H列（结果列）未被过滤或隐藏，虽然手动查找正常，但VBA对隐藏/过滤区域的处理可能存在差异。

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