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

Question

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"

or

"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

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Make sure you reference your cells properly instead of using `ActiveCell` or `Selection`. — Pᴇʜ, Oct 06 '21 at 08:49

score 0 · Answer 1 · answered Oct 06 '21 at 08:51

0

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

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

answered Oct 06 '21 at 08:51

Kohelet

394
1
6

Thanks for this, I'll join the strings with "&" operator too. – James Oct 06 '21 at 09:03

score 0 · Accepted Answer · answered Oct 06 '21 at 08:52

0

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

answered Oct 06 '21 at 08:52

Pᴇʜ

56,719
10
49
73

Thank you so much, that extra " seemed to be the problem. I'll read up on avoid using Select in Macros. – James Oct 06 '21 at 09:03

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

2 Answers2