VBA实现从区域最后一列VLookup遇到#NAME?错误求助

错误根因分析 #

你遇到的#NAME?报错和对象声明无关，核心原因有两个：

1. 公式字符串里直接写了VBA变量名myTableArray、Column1，这些是仅在VBA运行环境中有效的变量，Excel工作表公式无法识别，会被当成不存在的自定义名称，触发名称错误。
2. 计算最后一列的代码逻辑错误：原代码Range("A" & Columns.Count).End(xlToLeft).Column是从A列的最后一行单元格向左查找，得到的永远是1，无法正确获取数据透视表的最大列数。

修复方案 #

需要把VBA变量的实际值拼接到公式字符串中，同时修正最后一列的计算逻辑，优化后可直接去除Select操作避免页面抖动，代码更稳定：

Sub VLookupLastColumn()
    Dim Row7 As Long
    Dim Column1 As Long
    Dim myTableArray As Range
    Dim wsPivot As Worksheet
    Dim wsOutput As Worksheet
    
    ' 直接绑定工作表对象，避免Select操作
    Set wsPivot = Sheets("Pivot Values")
    Set wsOutput = Sheets("Output")
    
    With wsPivot
        ' 获取透视表最大行号
        Row7 = .Range("A" & .Rows.Count).End(xlUp).Row
        ' 修正：从第一行最右单元格向左查找，获取透视表最大列号
        Column1 = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ' 定义查找区域
        Set myTableArray = .Range(.Cells(1, 1), .Cells(Row7, Column1))
    End With
    
    ' 直接写入公式，将VBA变量值拼入公式字符串
    ' myTableArray.Address(External:=True) 会返回带工作表名的完整区域地址
    wsOutput.Range("BD5").FormulaR1C1 = "=VLOOKUP(RC[-55]," & myTableArray.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & Column1 & ",0)"
End Sub

关键修改说明 #

• 用Address(ReferenceStyle:=xlR1C1, External:=True)获取查找区域的R1C1样式完整地址，适配FormulaR1C1的格式要求，自动带上透视表所在工作表名称
• 把VBA变量Column1的数值直接拼接到公式的第三参数位置，工作表公式读取到的是实际的列号数字
• 移除了所有Select操作，避免工作表切换带来的不可预期错误

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