1

I'm trying to run the below code and it gives me

Run-Time Error "1004"

Application-defined or Object-defined error

Every Single Time!!

Attached is a snippet of the code, any suggestions what's wrong? (The numbers in the Range(Cells( * ) sections are actually mostly variables in my overall macro, it's pretty complex but I've taken them out for simplicity here)

Code:

'Declare variables
Dim CriteriaRng As String
Dim SumRng As String
Dim Criteria As String

'Set a variable for each of the 3 parts of the SUMIF Formula
CriteriaRng = "'" & Sheets(1).Name & "'!" & Range(Cells(2, 4), Cells(88, 4)).Address
SumRng = "'" & Sheets(1).Name & "'!" & Range(Cells(2, 3), Cells(88, 3)).Address
Criteria = Chr(34) & "=" & Chr(34) & " & RC[-1]"


'Here goes the SUMIF Formula
    With Sheets(2).Range(Cells(4, 13), Cells(9, 13))
        Debug.Print "So the Whole Formula Should be:" & Chr(13) & "= SUMIF(" & CriteriaRng & ", " & Criteria & ", " & SumRng & ")"
'That was a vain attempt to find out what was wrong with the formula; didn't work though.
        .FormulaR1C1 = "= SUMIF(" & CriteriaRng & ", " & Criteria & "," & SumRng & ")"
        
'Then adds NumberFormat and stuff here, but that isn't relevant to this question.
    End With

The error always hits on the line where it's putting in the .FormulaR1C1 = .

Yes, I know I could get the same result using a nested loop, but that would return just the value without the SUMIF formula - I need that formula so the sheet updates when edited (without needing a macro - I'm sending the sheet on to other people who won't have or want any macros, but might need to edit the data).

Can anyone point out to me what is wrong? I'm prepared for it to be something pretty basic - only last week I spent 2 hours figuring out a problem from misspelling 'Columns' !!!

Any and all advice welcome - Many Thanks in advance.

Community
  • 1
  • 1
Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
  • 4
    `Address` returns an A1 reference by default. If you need R1C1 style references, you should specify the `ReferenceStyle` argument. Although, since your row and column numbers are hardcoded, you might as well just use the relevant R1C1 strings directly in the code. – Rory Jan 16 '20 at 12:23
  • Ahaa I didn't pick up on the A1 / R1C1 incompatibility. I'll give that a try; Many thanks!!! – Spencer Barnes Jan 16 '20 at 12:42
  • Yep just tested it and all works - many many many thanks as I've sunk a good few hours into this macro already!!! – Spencer Barnes Jan 16 '20 at 12:45

0 Answers0