VBA中VLOOKUP含特殊字符|时出现Run-time Error 1004问题

先理清楚你的核心问题：原来的VLOOKUP公式代码运行完全正常，但数据表格式变更后，你尝试构造带竖线的新查询键（比如写了NewKey = Sheets("...")这类代码）时，直接触发了1004运行时错误。下面是几个最常见的排查方向和解决办法：

1. 公式拼接时的引号/竖线语法错误 #

这是最容易踩坑的点！当你在VBA里拼接包含竖线的公式时，必须注意Excel公式的语法规则，以及VBA里字符串转义的逻辑：

• Excel公式里，要把竖线作为字符串常量，需要用双引号包裹，比如$A1&"|"&$B1
• 但在VBA里，双引号需要转义成两个双引号""，否则会被VBA当成字符串的结束符

举个错误和正确的对比示例：

' ❌ 错误写法：竖线没有被正确作为字符串嵌入公式
Sheets("Sheet 1").Cells(i, 2).Formula = "=VLOOKUP($A" & i & "|" & $B" & i & ",Data!$A$1:$B$" & LastRow & "," & ColmNum & ",0)"

' ✅ 正确写法：用转义双引号把竖线包装成公式里的字符串常量
Dim lookupKeyStr As String
lookupKeyStr = "$A" & i & "&""|""&$B" & i ' 生成类似 "$A1&""|""&$B1" 的片段
Sheets("Sheet 1").Cells(i, 2).Formula = "=VLOOKUP(" & lookupKeyStr & ",Data!$A$1:$B$" & LastRow & "," & ColmNum & ",0)"

最终生成的单元格公式应该是=VLOOKUP($A1&"|"&$B1,Data!$A$1:$B$100,2,0)这类合法格式，你可以手动在单元格里测试这个公式是否能正常运行，再反推VBA拼接的问题。

2. 新键引用的数据源不存在或无效 #

如果你的NewKey代码里引用了错误的工作表、单元格范围，或者引用的单元格本身是错误值（比如#N/A、#VALUE!），也会触发1004错误：

• 检查工作表名称是否完全匹配（注意空格、大小写，比如Sheets("Sheet1")和Sheets("Sheet 1")是两个不同的表）
• 确认NewKey获取的单元格值是否有效，有没有引用超出数据范围的单元格

3. 公式长度超出Excel限制 #

Excel对单个公式的字符数有上限（Excel 2016及以后版本是8192字符），如果你的拼接后的公式太长，也会抛出1004错误。可以尝试：

• 把部分计算逻辑移到辅助单元格，再用VLOOKUP引用辅助单元格的结果
• 简化公式里的范围引用（比如用动态名称代替固定的$A$1:$B$" & LastRow）

4. 工作表保护导致写入权限不足 #

如果目标工作表（比如"Sheet 1"或"Data"表）被保护，且没有允许编辑公式所在的单元格，VBA尝试写入公式时会直接报错。你可以：

• 手动解除工作表保护后测试代码
• 在代码里添加自动解除/恢复保护的逻辑：

' 如果工作表有保护密码，替换成你的密码
Sheets("Sheet 1").Unprotect Password:="yourPassword"
' 执行写入公式的代码
Sheets("Sheet 1").Protect Password:="yourPassword"

你可以先重点排查公式拼接的语法问题，这是这类错误的高发区。如果还是解决不了，可以把你写的NewKey相关完整代码贴出来，这样能更精准定位问题。

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