VBA中使用变量调用Xlookup返回#NAME?错误的求助

问题原因 #

你直接在公式字符串里用VBA的LookupRng、SearchRng变量，Excel会把这些变量名当成未定义的名称或函数，所以返回#NAME?错误。Excel公式无法识别VBA变量，必须把Range对象转换成Excel能识别的单元格地址字符串。

另外你定义Range变量时没指定工作表（Set LookupRng = Range(...)），如果当前活动工作表不是shFollowup，会导致Range指向错误的工作表，这也是潜在隐患。

解决步骤 #

1. 给Range变量指定工作表：确保Range对象指向shFollowup工作表的目标范围。
2. 把Range转成地址字符串：用Range对象的Address方法，将其转换为Excel公式可识别的地址格式。如果需要填充公式时自动调整单元格引用，要使用相对地址（设置RowAbsolute:=False, ColumnAbsolute:=False）。

修正后的完整代码 #

Sub InsertXLookup()
    Dim LastRow_A As Long
    Dim LastRow_M As Long
    Dim LookupRng As Range
    Dim SearchRng As Range
    
    ' 定义两个范围的最后一行
    LastRow_A = shFollowup.Range("A1").End(xlDown).Row ' 输出范围的最后一行
    LastRow_M = shFollowup.Range("M1").End(xlDown).Row  ' 输出范围的最后一行
    
    ' 定义Xlookup范围，明确指定shFollowup工作表
    Set LookupRng = shFollowup.Range("C2:C" & LastRow_A)
    Set SearchRng = shFollowup.Range("A2:G" & LastRow_A)

    ' 原硬编码正常运行代码（可注释保留）
    ' shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2,C2:C200, A2:G200,""PO Added"",)"
    ' shFollowup.Range("N2:N" & LastRow_M).FillDown
     
    ' 修正后的代码：将Range变量转换为地址字符串插入公式
    shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2," & LookupRng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ", " & SearchRng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ",""PO Added"",)"
    shFollowup.Range("N2:N" & LastRow_M).FillDown

End Sub

补充说明 #

• 如果需要固定引用范围（填充公式时不改变范围），可以去掉RowAbsolute:=False, ColumnAbsolute:=False，使用默认的绝对引用（带$符号）。
• 要确保shFollowup是已正确定义的工作表对象（比如Dim shFollowup As Worksheet: Set shFollowup = ThisWorkbook.Worksheets("Followup")），避免因工作表未定义引发错误。

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