使用变量调用XLOOKUP时出现VBA运行时错误1004求助

错误原因分析 #

你的代码存在3个核心问题导致1004错误：

• 引号拼接错误：外部文件引用的公式被错误嵌套多余双引号，Excel无法解析无效语法
• 工作表引用混乱：计算lastRow时同时混用ActiveSheet和Sheets("OCI")，导致范围指向错误
• 赋值方式不当：用Selection.Value赋值公式不如直接用Range的Formula属性可靠，且Select/Selection容易引发上下文错误

修正后的代码 #

Dim lastRow As Long, filename As String, reportDate As String

Call getReportDate(reportDate) ' 用户输入报告日期的用户窗体

' 修正：明确用OCI工作表计算J列最后一行
lastRow = Sheets("OCI").Cells(Sheets("OCI").Rows.Count, "J").End(xlUp).Row
filename = "report_name_" & reportDate & ".xlsx"

' 修正：直接操作目标范围，避免Select/Selection，正确拼接公式
With Sheets("OCI").Range("V2:V" & lastRow)
    ' 用单引号包裹外部引用（兼容文件名/表名含空格的情况），正确拼接变量
    .Formula = "=XLOOKUP(A2, '[" & filename & "]Sheetname'!$L:$L, '[" & filename & "]Sheetname'!$N:$N)"
    ' 若使用Excel 365/2021，可改用动态数组公式：
    ' .Formula2 = "=XLOOKUP(A2:A" & lastRow & ", '[" & filename & "]Sheetname'!$L:$L, '[" & filename & "]Sheetname'!$N:$N)"
End With

关键修正点说明 #

1. 公式拼接逻辑：外部文件引用格式应为'[filename.xlsx]Sheetname'!$L:$L，用单引号包裹整个引用部分，直接插入filename变量，无需额外嵌套双引号
2. 工作表范围明确：所有Range操作都指定Sheets("OCI")，避免依赖ActiveSheet引发的上下文错误
3. 替换Select/Selection：通过With语句直接操作目标单元格区域，代码更高效且不易出错
4. Formula属性替代Value：用Formula属性赋值公式，Excel会自动解析语法，比Value更稳定

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