VBA - SUMIFS - Incrementing rows

[Jeofbist3]

Dear all,

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,

 

[Jonmo1]

Try putting the formula in all cells at once, instead of with a loop..

Range("I2:I" & LastRow).Formula = "=SUMIFS(H:H,G:G,G2,A:A,A2)"

 

[Jeofbist3]

Hi Jonmo1,

Indeed, this works, and it's definitely faster... Spent so much time on this loop that I've almost forgotten the simple things.

But, by any chance, do you have any clue on why this loop doesn't work ? And what would a simple code look like if I want to insert a loop (i'd like to practice).

Thanks for your time

 

[Jonmo1]

You're welcome.

Sure
Range("I2").Formula = "=SUMIFS(H:H,G:G,Range(i,7),A:A,Range(i,1)"'
This won't work because a cell formula doesn't know what the Range function is.
Remember, VBA isn't putting a formula in the cell persay. It's just putting a TEXT String into the cell, and the Excel Worksheet is interpraiting it as a formula.


Range(i, 9).Value = Application.WorksheetFunction.SumIfs(Range("H:H"), Range("G:G"), Range(i, 7), Range("A:A"), Range(i, 1))
This one doesn't work because Range(i, 7) isn't valid syntax.
Range expects a cell reference like A1, it can't use Row#,Column# syntax
That would need to be changed to Cells(i, 7)

 

[Jeofbist3]

That works also great when changing everything from Range(...) to Cells(..) indeed !

I can close the thread now I guess.

Thanks a lot,