VBA中使用Set变量实现VLOOKUP公式时报错及字符串引用问题

咱们来拆解你遇到的两个核心问题，逐个给出解决方案：

问题1：使用Name.Address时出现运行时错误1004 #

你用MsgBox Name.Address能拿到正确的A1格式地址，但拼到FormulaR1C1里就报错，核心原因是格式不兼容：

• FormulaR1C1要求公式里的单元格引用必须是R1C1格式（比如R2C5代表第2行第5列），但Name.Address默认返回的是A1格式（比如E2），两种格式不匹配，Excel自然抛出1004错误。

解决方案：指定Address的引用格式为xlR1C1 #

修改代码，给Address加上ReferenceStyle:=xlR1C1参数，让它返回R1C1格式的地址，就能和FormulaR1C1完美适配了：

Worksheets("E'ee Details").Range("N1").FormulaR1C1 = "=VLOOKUP(" & Name.Address(ReferenceStyle:=xlR1C1) & ",EmployeeBank!C[-13]:C[-10],4,FALSE)"

问题2：直接使用Name变量时，文本值缺少引号导致公式失效 #

当你直接用& Name &拼接时，公式变成=VLOOKUP(J Bob,EmployeeBank!A:D,4,FALSE)，这里J Bob是文本，但Excel会把它当成未定义的名称/单元格引用，而非字符串，所以公式无法正常工作。

你尝试过加引号但没成功，大概率是拼接方式不对。VBA里要在字符串中输出双引号，需要用两个双引号转义，或者用Chr(34)（双引号的ASCII码）。下面是两种正确写法：

方法1：双引号转义法 #

Worksheets("E'ee Details").Range("N1").FormulaR1C1 = "=VLOOKUP(""" & Name & """,EmployeeBank!C[-13]:C[-10],4,FALSE)"

这里的"""逻辑是：外层双引号包裹公式字符串，中间两个双引号代表一个实际的双引号，最终公式里会生成"J Bob"。

方法2：Chr(34)变量法 #

Dim quote As String
quote = Chr(34)
Worksheets("E'ee Details").Range("N1").FormulaR1C1 = "=VLOOKUP(" & quote & Name & quote & ",EmployeeBank!C[-13]:C[-10],4,FALSE)"

这种方式更直观，把双引号存成变量，拼接时直接调用，不容易搞混引号数量。

额外小技巧：根据习惯选择公式属性 #

如果你更偏爱A1格式的公式，可以改用Formula属性代替FormulaR1C1，这样用Name.Address（默认A1格式）就不会出错了：

Worksheets("E'ee Details").Range("N1").Formula = "=VLOOKUP(" & Name.Address & ",EmployeeBank!A:D,4,FALSE)"

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