DeanP
02-07-2019, 07:32 AM
I am attempting to write a sumif formula in R1C1 style to use in a macro. Don't know what I am doing wrong, because I can't get the sumif to work in VBA.
All my ranges are dynamic:
(a) Criteria range = D7 to last row [LRow = Cells(Rows.Count, "D").End(xlUp).Row]
(b) Criteria lookup = O7 to last row [DrLRow = Cells(Rows.Count, "O").End(xlUp).Row]
(c) Sum range = I7 to last row
(d) Formula result in P7 to last row Col O
My formula is =SUMIF(D7 : D22,O7,I7:I22)
I used the macro recorder to try and help me and I got this:
ActiveCell.FormulaR1C1 = "=SUMIF(R7C4:R22C4,RC[-1],R7C9:R22C9)"
My attempt in VBA is:
LRow = Cells(Rows.Count, "D").End(xlUp).Row
drLRow = Cells(Rows.Count, "P").End(xlUp).Row
Range("P7" & drLRow).FormulaR1C1 = "=SUMIF(R7C4:R" & LRow & "RC[-1],R7C9" & LRow & "I)"
Unsurprisingly, this doesn't work. What am I doing wrong here?
All my ranges are dynamic:
(a) Criteria range = D7 to last row [LRow = Cells(Rows.Count, "D").End(xlUp).Row]
(b) Criteria lookup = O7 to last row [DrLRow = Cells(Rows.Count, "O").End(xlUp).Row]
(c) Sum range = I7 to last row
(d) Formula result in P7 to last row Col O
My formula is =SUMIF(D7 : D22,O7,I7:I22)
I used the macro recorder to try and help me and I got this:
ActiveCell.FormulaR1C1 = "=SUMIF(R7C4:R22C4,RC[-1],R7C9:R22C9)"
My attempt in VBA is:
LRow = Cells(Rows.Count, "D").End(xlUp).Row
drLRow = Cells(Rows.Count, "P").End(xlUp).Row
Range("P7" & drLRow).FormulaR1C1 = "=SUMIF(R7C4:R" & LRow & "RC[-1],R7C9" & LRow & "I)"
Unsurprisingly, this doesn't work. What am I doing wrong here?