Search code examples
vbaincrementsumifs

VBA - SUMIFS - Incrementing row - error 1004

I've just registered on the website and I'm sure I can get some help on the below issue (I'm not an expert in VBA)..

Briefly, I'm doing a macro that computes a total quantity (in column I, for each row) based on 2 parameters (column A and column G). I'm using a SUMIFS function, which works great for one cell. But, I'd like to automate it for every row, that becomes tricky for me..

Here is the code that works for cell I2 : 

Sub quantity_aggregated()      
    Dim sht As Worksheet, LastRow As Long, i As Integer    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row     
    Set sht = ThisWorkbook.Worksheets("JDE_Greece")     
    Range("I2").Formula = "=SUMIFS(H:H,G:G,G2,A:A,A2)"

I want to keep it that way but integrate a For, starting at row 2 and finishing at the last populated row.

I thought (and tried) about some codes below, 3 different (the end is the same, incrementing i and close the macro) : 

For i = 2 To LastRow     

'Range("I2").Formula = "=SUMIFS(H:H,G:G,Range(i,7),A:A,Range(i,1)"''      

'Range(i, 9).Value = Application.WorksheetFunction.SumIfs(Range("H:H"), Range("G:G"), Range(i, 7), Range("A:A"), Range(i, 1))'   

'Cells(i, 9).FormulaR1C1 = "=SUMIFS(H:H,G:G, "'     

Next i      
End Sub

I understand the process but for each, there's a "method range of object global failed (error 1004)" that pops up. I'm sure it's something about defining the application, the object or something related but can't solve it.

Does anyone have any ideas ? It would be really great :)

Thanks a lot in advance,

Solution

  • You can simply apply the formula to the whole range I. since your references to A2 and G2 are "relative", the formula will systematically adjust for each row in the range.

    Dim sht As Worksheet, LastRow As Long
Set sht = ThisWorkbook.Worksheets("JDE_Greece")     
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row     
sht.Range("I2:I" & LastRow).Formula = "=SUMIFS(H:H,G:G,G2,A:A,A2)"

    If you want to do it with a loop "for practice",

    For i = 2 To LastRow
  sht.Range("I" & i).Formula = "=SUMIFS(H:H, G:G, G" & i & ", A:A, A" & i & ")"
Next i

    but notice that this is exactly what happens behind the scenes in the first form.