VBA - Using cell value as a part of a range and dragging and dropping.

[PGD15]

Hi there,

I am a bit of a newb when it comes to VBA. I've tried many different options and nothing worked. I cannot think of a way to get this to work. Would appreciate any help:

Aim:
I wish to paste a sum formula in Sheet1, Column R starting in Row 2. which sums the previous 4 columns (simple enough). However, I wish for my VBA code to drag and drop this sum formula down a row number which is variable based on the value in Sheet2 A1.

Basically something like worksheets("Sheet1").range("R2").formula = "=sum(N2:Q2)" then I want to repeat this down to the value of Sheet2 A1 which will always be a number (this cell has a formula in it so will be variable).
Ie i wish to "drag an drop" it down.

OR
Everything is the same but ignoring the value in Sheet2 A1, i wish for the formula to be dragged down to the last row where there is a data in column Q.

In simple R2 should have the formula =sum(N2:Q2). R3 =sum(N3:Q3) and so on but repeated down to a row where i don't know where it will end.

Hope this makes sense, any suggestions would be appreciated.


EDIT:
I think its something like the below but i don't know how to change the end value of the range from a predefined value to a variable or where data is last present in the column Q (RC-1)

ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("R1").Select
Selection.AutoFill Destination:=Range("R1:R2255"), Type:=xlFillDefault
Range("R1:R2255").Select

 

[Joe4]

No autofill needed.
Try this single line of code:

Range("R1:R" & Cells(Rows.Count, "Q").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

 

[PGD15]

No autofill needed.
Try this single line of code:

Range("R1:R" & Cells(Rows.Count, "Q").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

This worked perfectly thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!