VBA 런타임 오류 '1004': 응용 프로그램 정의 또는 개체 정의 오류" 설정 셀 내용 (VBA Runtime Error '1004': Application-defined or Object-defined error" setting cell contents)

문제 설명

VBA 런타임 오류 '1004': 응용 프로그램 정의 또는 개체 정의 오류" 설정 셀 내용 (VBA Runtime Error '1004': Application‑defined or Object‑defined error" setting cell contents)

셀 내용을 설정할 때, 특히 줄의 셀 내용을 설정할 때 오류 1004가 발생합니다.

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

동일한 코드가 여러 시트에서 사용되며 일부 작업에서는 이 오류가 발생합니다. 기본적으로 "21BG"로 시작하는 참조 코드가 있는 행의 총계를 추가한 다음 설명을 인쇄하려고 합니다.

"총 GBP £TOTAL 증가"

또는

"총 GBP £TOTAL 감소" 합계에 따라 다릅니다.

매크로는 다음과 같습니다.

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

참조 솔루션

방법 1:

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

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

방법 2:

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

(by James、Kohelet、Pᴇʜ)

참조 문서

1. VBA Runtime Error '1004': Application‑defined or Object‑defined error" setting cell contents (CC BY‑SA 2.5/3.0/4.0)