VBA Formula with dynamic range.

[leemcder]

Hi, I'm struggling with a formula in VBA. I need a formula for a dynamic range. This is the formula I have in my spreadsheet =SUM(P10:Q16)/(SUM(C10:C16)-SUM(D10:16))

I need help with the code below, which I need to put in the macro. I want it to calculate the cells from row 5 to the last row. The code below doesn't work, but should hopefully show you roughly what I need? I need it to work like the formula above, but with a dynamic range. Can anyone please help?

With Sheets("RTA") 'put your sheet name here
lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("U" & lLastRow + 2).Formula = "=Sum(P5:Q" & lLastRow & ")/(Sum(c5:c" & lLastRow & ")-Sum(d5:d" & lLastRow & "))"
End With


*this post has also been placed on http://www.vbaexpress.com/forum/showthread.php?64478-VBA-Formula-with-dynamic-range-Please-help_{^{<strike>
</strike>}}​

 

[Special-K99]

=SUM(P10:Q16)/(SUM(C10:C16)-SUM(D10:16))

The last SUM.. no ending column reference

Presumably this should be =SUM(P10:Q16)/(SUM(C10:C16)-SUM(D10:D16))

 

[leemcder]

Hi, I'm struggling with a formula in VBA. I need a formula for a dynamic range. This is the formula I have in my spreadsheet =SUM(P10:Q16)/(SUM(C10:C16)-SUM(D10:16))

I need help with the code below, which I need to put in the macro. I want it to calculate the cells from row 5 to the last row. The code below doesn't work, but should hopefully show you roughly what I need? I need it to work like the formula above, but with a dynamic range. Can anyone please help?

With Sheets("RTA") 'put your sheet name here
lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("U" & lLastRow + 2).Formula = "=Sum(P5:Q" & lLastRow & ")/(Sum(c5:c" & lLastRow & ")-Sum(d5:d" & lLastRow & "))"
End With


*this post has also been placed on http://www.vbaexpress.com/forum/showthread.php?64478-VBA-Formula-with-dynamic-range-Please-help_{^{<strike>
</strike>}}​

Solved it myself - code below worked

With Sheets("RTA") 'put your sheet name here
    lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("U" & lLastRow + 2).Formula = "=Sum(P5:Q" & lLastRow & ")/(Sum(c5:c" & lLastRow & ")-Sum(d5:d" & lLastRow & "))"
End With[LEFT][COLOR=#222222][FONT=Tahoma]

[/FONT][/COLOR][/LEFT]

 

[Norie]

Try using R1C1 notation.

With Sheets("RTA") 'put your sheet name here
    lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("U" & lLastRow + 2).FormulaR1C1 = "=Sum(R5C[-5]:R[-2]C[-4])/(Sum(R5C[-18]:R[-2]C[-18])-Sum(R5C[-18]:R[-2]C[-17]))"
End With

 

[leemcder]

Try using R1C1 notation.

With Sheets("RTA") 'put your sheet name here
    lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("U" & lLastRow + 2).FormulaR1C1 = "=Sum(R5C[-5]:R[-2]C[-4])/(Sum(R5C[-18]:R[-2]C[-18])-Sum(R5C[-18]:R[-2]C[-17]))"
End With

Thank you