VBA公式中无法插入字符串变量的问题排查

我尝试在VBA中用变量引用另一个工作簿的命名范围来编写VLOOKUP公式，试过添加/去掉双引号、改用RC单元格格式都无法生效。已搜索相关方案但未解决，希望搞定当前的公式写法问题。

Dim cwb as workbook ' 这是不同于当前工作簿的另一个工作簿
Dim crng as range
Dim frmcount as long
Dim checkcsht as string, cwbname as string

    Set crng = csht.Range("D:P")
    checkcsht = "crng"
    cwb.Names.Add Name:=checkcsht, RefersTo:=crng
    
    cwbname = cwb.Name
    cwbname = "'[" & Left(cwb.Name, Len(cwb.Name) - 5) & "]'!crng"
    Debug.Print cwbname
    
'从cont-report获取铁路数据
    frmcount = Application.CountA(frmsht.Range("A:A"))
    With frmsht.Range("N2")
        .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-11],""" & cwbname & """, 12, False)), """", (VLOOKUP(RC[-11],""" & cwbname & """, 12, False)))"
        .AutoFill Destination:=.Parent.Range("N2:N" & frmcount), Type:=xlFillDefault
        Calculate
        .EntireColumn.Copy
        .EntireColumn.PasteSpecial xlPasteValues
    End With

所有变量均已正确声明和赋值，请问我哪里出错了？

你的问题核心出在命名范围的引用格式和FormulaR1C1的字符串拼接逻辑上：

1. 手动拼接的范围引用不可靠

   • 你通过Left(cwb.Name, Len(cwb.Name) -5)截取文件名的写法，默认了文件后缀是5个字符（比如.xlsx），但如果是.xlsm、.xls等后缀，长度会不一致，导致拼接出的范围地址错误。
   • 正确做法是直接用crng.Address(External:=True, ReferenceStyle:=xlR1C1)获取带工作簿、工作表的R1C1格式完整外部引用，无需手动拼接。

2. FormulaR1C1的引号转义错误

   • 你当前代码中""" & cwbname & """的写法，会给变量拼接的范围地址额外套上双引号，导致Excel无法识别合法的范围引用，公式自然失效。去掉多余的引号转义即可。

修正后的代码片段：

' 替换原cwbname赋值逻辑
cwbname = crng.Address(External:=True, ReferenceStyle:=xlR1C1)

With frmsht.Range("N2")
    ' 简化公式拼接，去掉多余引号转义
    .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-11]," & cwbname & ",12,FALSE)),"""",VLOOKUP(RC[-11]," & cwbname & ",12,FALSE))"
    .AutoFill Destination:=.Parent.Range("N2:N" & frmcount), Type:=xlFillDefault
    Calculate
    ' 直接赋值替代复制粘贴，更高效
    .EntireColumn.Value = .EntireColumn.Value
End With

另外可以用IFERROR简化公式写法，让代码更简洁：

.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-11]," & cwbname & ",12,FALSE),"""")"

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