SumIF with dynamic selections using VBA

[kjohn25744]

I need to sum a row using the sumIf function, however the number of rows change depending values selected on another sheet. I have a variable the calculates the last row (lRow) and would like to use that to calculate the offset for the value in VBA. Currently the VBA code is as follows:

ActiveCell.FormulaR1C1 = "=SUMIF(R[-l5]C[1]:R[-3]C[1],""Y"",R[-15]C:R[-3]C)

I would like to replace the [-15] with something like this [lRow -18] This would get me the correct offset of -15. I have tried several combinations, but can seen to get it to work. Not sure this is possible or not. Any ideas?

 

[Fluff]

You can use a variable like

ActiveCell.FormulaR1C1 = "=SUMIF(R[" & lrow - 18 & "]C[1]:R[-3]C[1],""Y"",R[" & lrow - 18 & "]C:R[-3]C)"