VLOOKUP公式文本前自动加单引号且触发运行时错误'1004'的宏问题

结合你给出的代码场景和遇到的问题，我来拆解并给出针对性的解决方案：

一、文本前自动加单引号的原因与修复 #

你碰到的VLOOKUP结果带单引号，大概率是公式构造时的字符串拼接错误，或者是对外部引用的自动格式误解：

1. 避免手动给查找值加多余单引号 #

如果你的VLOOKUP是通过字符串拼接生成的，像下面这种错误写法会导致查找值被包裹成'ABC'，Excel会把它当成纯文本而非匹配值：

' 错误示例：手动给查找值加了单引号
Range("A1").Formula = "=VLOOKUP('" & Range("B1").Value & "', " & wb_Final.Sheets("Data").Range("A:C").Address(External:=True) & ", 3, FALSE)"

正确的做法是直接引用单元格，或者用双引号转义来表示文本值：

' 推荐：直接引用单元格，让Excel自动处理格式
Range("A1").Formula = "=VLOOKUP(B1, " & wb_Final.Sheets("Data").Range("A:C").Address(External:=True) & ", 3, FALSE)"

' 如果必须用固定文本作为查找值：
Dim lookupText As String
lookupText = "每周汇总"
Range("A1").Formula = "=VLOOKUP(""" & lookupText & """, " & wb_Final.Sheets("Data").Range("A:C").Address(External:=True) & ", 3, FALSE)"

这里用"""转义公式里的双引号，确保Excel识别为正常文本值，而非单引号包裹的字符串。

2. 外部引用的自动单引号是正常语法 #

如果是工作簿/工作表名称带空格（比如Weekly Summary.xlsx），Excel会自动给外部引用加单引号（比如'[Weekly Summary.xlsx]Data'!$A:$C），这是合法的语法，不会影响公式运行，不需要手动移除。

二、运行时错误'1004'的排查与修复 #

错误1004在VBA操作Excel时非常常见，结合你的代码场景，重点排查这几点：

1. 确认工作表名称绝对准确 #

你的代码里写了wb_Summary.Sheets("...")，这里的"..."必须和目标工作表的名称完全一致（包括大小写、空格、特殊字符）。建议先验证工作表是否存在：

Dim targetSheet As Worksheet
On Error Resume Next
Set targetSheet = wb_Summary.Sheets("你的目标表名") ' 替换成实际名称
On Error GoTo 0
If targetSheet Is Nothing Then
    MsgBox "Summary工作簿里找不到目标工作表！"
    Exit Sub
End If

2. 确保工作簿路径有效且文件可访问 #

检查Final_Directory和Summary_Directory是否是完整的文件路径（比如包含.xlsx扩展名），且文件没有被其他程序锁定、你有读写权限。可以添加错误捕获避免崩溃：

On Error Resume Next
Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
On Error GoTo 0
If wb_Final Is Nothing Then
    MsgBox "打不开文件：" & Final_Directory
    Exit Sub
End If

3. 公式语法错误导致的1004 #

如果公式本身有语法问题（比如缺括号、引用无效），插入时会直接报错。建议先在Excel手动输入公式验证能正常运行，再转换成VBA字符串。

三、优化后的完整代码示例 #

结合以上修复点，给你一段可参考的优化代码：

Sub AutoWeeklyVLOOKUP()
    Dim wb_Final As Workbook, wb_Summary As Workbook
    Dim targetSheet As Worksheet
    Dim Final_Directory As String, Summary_Directory As String
    Dim lastRow As Long
    
    ' 这里保留你通过msoFileDialogFilePicker获取路径的代码
    ' ...
    
    ' 打开并验证Final工作簿
    On Error Resume Next
    Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
    On Error GoTo 0
    If wb_Final Is Nothing Then
        MsgBox "无法打开最终文件：" & Final_Directory
        Exit Sub
    End If
    
    ' 打开并验证Summary工作簿
    On Error Resume Next
    Set wb_Summary = Workbooks.Open(Filename:=Summary_Directory)
    On Error GoTo 0
    If wb_Summary Is Nothing Then
        MsgBox "无法打开汇总文件：" & Summary_Directory
        wb_Final.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' 验证目标工作表存在
    On Error Resume Next
    Set targetSheet = wb_Summary.Sheets("汇总表") ' 替换为实际表名
    On Error GoTo 0
    If targetSheet Is Nothing Then
        MsgBox "汇总工作簿中找不到指定工作表！"
        wb_Summary.Close SaveChanges:=False
        wb_Final.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' 插入VLOOKUP公式（示例：A列匹配B列值到Final工作簿的Data表）
    With targetSheet
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("A2:A" & lastRow).Formula = _
            "=VLOOKUP(B2, " & wb_Final.Sheets("Data").Range("A:C").Address(External:=True) & ", 3, FALSE)"
        ' 可选：把公式结果转为值，彻底避免格式问题
        .Range("A2:A" & lastRow).Value = .Range("A2:A" & lastRow).Value
    End With
    
    MsgBox "公式插入完成！"
    ' 根据需求决定是否保存关闭
    wb_Summary.Close SaveChanges:=True
    wb_Final.Close SaveChanges:=False
End Sub

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