VBA创建含字符串、单元格及工作表引用的公式报错求助

错误原因 #

你当前的代码错误地将VBA变量Consol_Sheet.Name直接嵌入到公式字符串内部，Excel会把它当成公式里的未知名称，从而抛出#NAME?错误；同时公式的拼接逻辑也不符合Excel公式的语法要求。

正确代码实现 #

要生成目标公式= "Sample: " & 'Test 1 - 520 psf'!B5，需要通过VBA字符串拼接将工作表名变量的值插入到公式中，同时处理好引号转义：

Dim Consol_Sheet As Worksheet
Set Consol_Sheet = Sheets("Test 1 - 520 psf")
' 拼接公式字符串：将工作表名变量插入，同时处理Excel公式的引号和工作表引用格式
ActiveSheet.Range("B3:J3").FormulaR1C1 = "= ""Sample: "" & '" & Consol_Sheet.Name & "'!R[2]C"

代码说明 #

• ""Sample: ""：在VBA中用两个双引号表示Excel公式里的一个双引号，对应公式中的"Sample: "
• '" & Consol_Sheet.Name & "'!：将工作表名用单引号包裹（因为表名含空格），通过VBA字符串拼接把变量值插入到公式中，对应公式里的'Test 1 - 520 psf'!
• R[2]C：R1C1格式的引用，对应当前单元格所在列向下2行的单元格（比如B3对应的就是B5）

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