VBA运行时错误'1004'：设置单元格内容触发应用程序/对象定义错误

核心报错原因 #

你触发错误的根因有3个，对应部分工作表运行正常、部分报错的不稳定现象：

• 公式语法错误：出错行末尾多了冗余双引号，你写的ActiveCell.FormulaR1C1 = "=""Total increase in GBP ""&MENU!R11C10"""最后多了1个双引号，VBA解析公式时语法不合法，部分工作表因局部格式兼容未触发，但逻辑本身不稳定
• 操作逻辑依赖不稳定：代码全靠Select、ActiveCell定位单元格，不同工作表的激活状态、单元格位置不同，当Offset跳转后落到被保护、合并、超出合法范围的单元格时，就会触发对象定义错误
• 潜在引用异常：部分工作表所属的工作簿不存在名为MENU的工作表，或是MENU表J11单元格（对应R11C10）为错误值（如#REF!、#N/A），会直接导致公式赋值失败

修复后的完整代码 #

已优化所有不稳定逻辑，兼容所有工作表场景：

Sub AdjustGBP()
    Dim ws As Worksheet, menuWs As Worksheet
    Dim lastRowB As Long, totalVal As Double
    Dim gbpTotalStr As String
    
    ' 绑定当前操作工作表，校验MENU表是否存在
    Set ws = ActiveSheet
    On Error Resume Next
    Set menuWs = ThisWorkbook.Worksheets("MENU")
    On Error GoTo 0
    If menuWs Is Nothing Then
        MsgBox "未找到MENU工作表，无法执行", vbCritical
        Exit Sub
    End If
    ' 提前读取MENU表J11的值，避免公式引用报错
    gbpTotalStr = menuWs.Range("J11").Text
    
    ' 定位B列最后一行，兼容新旧版本Excel的行数量限制
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
    ws.Cells(lastRowB, "B").Value = "21BG"
    
    ' 直接赋值替代复制粘贴，提升运行效率
    ws.Cells(lastRowB, "C").Value = ws.Cells(lastRowB - 1, "C").Value
    ws.Cells(lastRowB, "D").Value = "11041202"
    ws.Cells(lastRowB, "E").Value = "Current deposits GBP"
    ws.Cells(lastRowB, "F").Value = "当座預金 GBP"
    
    totalVal = ws.Cells(lastRowB, "K").Value
    If totalVal > 0 Then
        ' 执行减法操作，替代选择性粘贴
        ws.Cells(lastRowB, "I").Value = ws.Cells(lastRowB, "I").Value - totalVal
        ' 直接拼接文本，无需写入公式
        ws.Cells(lastRowB, "G").Value = "Total increase in GBP " & gbpTotalStr
    Else
        ws.Cells(lastRowB, "J").Value = ws.Cells(lastRowB, "J").Value - totalVal
        ws.Cells(lastRowB, "G").Value = "Total decrease in GBP " & gbpTotalStr & " 2021"
    End If
    
    ' 统一设置边框
    With ws.Range(ws.Cells(lastRowB - 3, "B"), ws.Cells(lastRowB, "K")).Borders
        .Item(xlEdgeBottom).Weight = xlMedium
        .Item(xlInsideHorizontal).Weight = xlThin
    End With
End Sub

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