Excel 2010 VBA：能否在另一公式引用WorksheetFunction结果？报错2029求助

首先，咱们先拆解你代码里的几个关键问题，这些都是导致Error 2029（对应Excel的#NAME?错误）的核心原因：

1. 语法错误：错误使用Set语句 #

Set是用来给对象变量赋值的，但ANoteColID和BNoteColId是存储单元格值的普通变量，不是对象，完全不需要用Set，直接赋值即可。

2. 方括号[]的局限性 #

你用[VLOOKUP(...)]这种写法其实是调用Evaluate方法，但这种方式在引用变量（比如ANoteColID）时会出问题——方括号里的内容会被当成纯字符串，不会解析变量的实际值，这就导致MATCH函数找不到正确的匹配目标。

3. 数组运算无法直接用WorksheetFunction #

你想匹配ANoteColID & 2和CollateralData!AK3:AK500 & CollateralData!CZ...的组合，这属于数组运算，但WorksheetFunction.MATCH不支持直接处理这种区域拼接的数组，必须借助Evaluate来实现。

修正后的完整代码示例 #

Sub FixLookupIssues()
    Dim ANoteColID As Variant
    Dim BNoteColId As Variant
    Dim wsPriorYear As Worksheet
    Dim wsCollateral As Worksheet
    
    ' 先定义工作表对象，避免硬编码工作表名导致的错误
    Set wsPriorYear = ThisWorkbook.Sheets("Prior Year")
    Set wsCollateral = ThisWorkbook.Sheets("CollateralData")
    
    ' 执行VLOOKUP：用Application.VLOOKUP代替WorksheetFunction，避免找不到匹配时直接抛出运行时错误
    ANoteColID = Application.VLOOKUP(wsPriorYear.Range("C4").Value, _
                                    wsCollateral.Range("G3:CZ500"), _
                                    31, _
                                    False)
    
    ' 检查VLOOKUP是否返回错误（比如没找到匹配）
    If IsError(ANoteColID) Then
        MsgBox "VLOOKUP未找到匹配值，请检查Prior Year工作表的C4单元格内容"
        Exit Sub
    End If
    
    ' 执行MATCH：用Evaluate处理区域拼接的数组运算
    BNoteColId = Application.Evaluate("MATCH(""" & ANoteColID & "2"", " & _
                                    wsCollateral.Range("AK3:AK500").Address(External:=True) & "&" & _
                                    wsCollateral.Range("CZ3:CZ500").Address(External:=True) & ", 0)")
    
    ' 检查MATCH是否返回错误
    If IsError(BNoteColId) Then
        MsgBox "MATCH未找到匹配值，请检查CollateralData工作表的AK和CZ列内容"
        Exit Sub
    End If
    
    ' 这里可以添加后续逻辑，比如使用获取到的BNoteColId
    MsgBox "找到的匹配行号为：" & BNoteColId + 2 ' 因为从第3行开始，加2得到实际工作表行号
End Sub

关键注意点 #

• Application vs Application.WorksheetFunction：前者在函数返回错误（比如VLOOKUP没找到）时会返回错误值，你可以用IsError检查；后者会直接抛出运行时错误，需要用On Error捕获，前者更灵活。
• 变量拼接进Evaluate：要把变量值转换成字符串拼入公式，比如""" & ANoteColID & "2"""是为了给匹配目标加上引号，确保Excel识别为文本。
• 区域引用：用Address(External:=True)可以避免工作表名冲突，确保公式能正确指向目标区域。

内容的提问来源于stack exchange，提问作者D.Mascari