PDA

View Full Version : VBA Formula with dynamic range. Please help



leemcder
01-21-2019, 02:11 PM
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:D16))

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

leemcder
01-22-2019, 02:26 AM
*This post has also been placed here https://www.mrexcel.com/forum/excel-questions/1084632-vba-formula-dynamic-range.html#post5211919

leemcder
01-22-2019, 04:15 AM
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:D16))

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

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