VBA Runtime Error '1004': Application-defined or Object-defined error" setting cell c-CodePudding

I am encountering the Error 1004 when setting cell contents, specifically when setting the cell contents in the line

 ActiveCell.FormulaR1C1 = "=""Total increase in GBP ""&MENU!R11C10"""

The same code is used in multiple sheets and some work whereas others encounter this error. Basically I'm trying to add the total of rows with a reference code beginning with "21BG", then print a description

"Total increase in GBP £TOTAL"

"Total decrease in GBP £TOTAL" depending on the total.

The macro is as follows:

Sub AdjustGBP()
'
' Macro4 Macro


    Range("B65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "21BG"

    ActiveCell.Offset(-1, 1).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
  
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "11041202"
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Current deposits GBP"

    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "当座預金 GBP"
   
    ActiveCell.Offset(0, 5).Select
    Selection.Copy
    If ActiveCell.Value > 0 Then

    ActiveCell.Offset(0, -2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract
    
    ActiveCell.Offset(0, -2).Select
'    ActiveCell.FormulaR1C1 = "Total increase in GBP XXX 2007"
    ActiveCell.FormulaR1C1 = "=""Total increase in GBP ""&MENU!R11C10"""
    
    Else
    
    ActiveCell.Offset(0, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract

    ActiveCell.Offset(0, -3).Select
'    ActiveCell.FormulaR1C1 = "=""Total Decrease in " & "MENU!R11C10""
    ActiveCell.FormulaR1C1 = "=""Total Decrease in GBP ""&MENU!R11C10&"" 2021"""

End If

    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range(ActiveCell.Offset(-3, -5), ActiveCell.Offset(0, 4)).Select
    With Selection.Borders(xlEdgeBottom)
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideHorizontal)
        .Weight = xlThin
    End With
    
End Sub

CodePudding user response：

Don't forget to join strings using "&" operator.

ActiveCell.FormulaR1C1 = "=" & Chr(34) & "Total increase in GBP " & Chr(34) & "&MENU!R11C10"

CodePudding user response：

This line

ActiveCell.FormulaR1C1 = "=""Total increase in GBP ""&MENU!R11C10"""

would look as formula like

="Total increase in GBP "&MENU!R11C10"

so it has one " in the end that should not be there!

The line should look like

ActiveCell.FormulaR1C1 = "=""Total increase in GBP ""&MENU!R11C10"

to get this formula

="Total increase in GBP "&MENU!R11C10