Formula in VBA: Can't make it myself. Please help.

[pedie]

Hi, I have this formula. How can i write this in VBA to pick the range till last row taking col B as primary key. For now it is picking up till 79 row.

'=SUM(INT($J$3/$J$4*G3),IF(RANK(I3,$I$3:$I$79,0)+COUNTIF(I$3:I3,I3)-1<=($J$3-SUMPRODUCT(INT($J$3/$J$4*$G$3:$G$79))),1,0))

Thanks in Adv.
Pedie

 

[VoG]

Hi Pedie. Try like this

LR = Range("B" & Rows.Count).End(xlUp).Row
ActiveCell.Formula = "=SUM(INT($J$3/$J$4*G3),IF(RANK(I3,$I$3:$I$" & LR & ",0)+COUNTIF(I$3:I3,I3)-1<=($J$3-SUMPRODUCT(INT($J$3/$J$4*$G$3:$G$" & LR & "))),1,0))"

 

[pedie]

Hi Peter, that was quick. Thank you soo much!!!
Pedie