如何在Excel VBA循环中写入跨工作表除法公式？解决#NAME错误

错误原因分析 #

#NAME?错误通常源于公式引用格式不符合Excel规则，常见触发场景：

• 工作表名称含空格、特殊字符（如&、(）时未用英文单引号包裹
• 公式字符串拼接错误，缺失工作表/单元格引用的必要分隔符（如!）
• 误使用中文引号或中文符号，Excel无法识别

正确实现代码示例 #

方法1：循环逐个写入公式（适配小数据量） #

Sub WriteFormulasLoop()
    Dim targetWs As Worksheet
    Dim constantWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' 绑定目标工作表和常量所在工作表
    Set targetWs = ThisWorkbook.Worksheets("数据") ' 替换为你的目标表名
    Set constantWs = ThisWorkbook.Worksheets("常量表") ' 替换为你的常量表名
    
    ' 获取B列数据最后一行（假设左侧数据在B列）
    lastRow = targetWs.Cells(targetWs.Rows.Count, "B").End(xlUp).Row
    
    ' 循环写入公式到C列（当前单元格左侧为B列）
    For i = 2 To lastRow
        ' 用英文单引号包裹工作表名，兼容特殊表名
        targetWs.Cells(i, 3).Formula = "=" & targetWs.Cells(i, 2).Address(False, False) & _
                                      "/'" & constantWs.Name & "'!" & constantWs.Range("A1").Address(False, False)
    Next i
End Sub

方法2：批量赋值公式（高效处理大数据量） #

避免循环逐个写入，直接给整列区域赋值公式，性能更优：

Sub WriteFormulaBatch()
    Dim targetWs As Worksheet
    Dim constantWs As Worksheet
    Dim targetRange As Range
    Dim constantCellAddr As String
    
    Set targetWs = ThisWorkbook.Worksheets("数据")
    Set constantWs = ThisWorkbook.Worksheets("常量表")
    
    ' 生成带工作表名的常量单元格引用（自动处理特殊表名）
    constantCellAddr = "'" & constantWs.Name & "'!" & constantWs.Range("A1").Address
    
    ' 定义要写入公式的区域（C2到C列最后一行）
    Set targetRange = targetWs.Range("C2:C" & targetWs.Cells(targetWs.Rows.Count, "B").End(xlUp).Row)
    
    ' 批量赋值公式，Excel自动调整相对引用
    targetRange.Formula = "=B2/" & constantCellAddr
End Sub

关键注意事项 #

• 公式所有符号必须使用英文半角，包括引号、感叹号、斜杠
• 工作表名含空格或特殊字符时，必须用'工作表名'!格式包裹
• 使用Address方法动态生成单元格引用，避免手动拼接字符串出错
• 确保常量工作表存在，且目标单元格（如A1）有有效数值（非空值/文本）

错误与正确公式对比 #

错误写法（触发#NAME?）：
=B2/常量表A1 （缺少!分隔符，表名未加单引号）

正确写法：
=B2/'常量表'!A1

效果截图 #

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