How to Auto FIll formula with dynamic range

Warpug

New Member
Joined
Apr 13, 2017
Messages
15
So I am currently working on a sheet into which I would like to place this formula into cell Q6.
=ROUND(SUMIFS($P$6:$P$97,$H$6:$H$97,$H6),2)=0
what I am trying to figure out, is how do write this in vba so that it enters the formula in all the column q for every row. So the issue is that the p97 and h97 are dynamic and change regularly. Thank you.
Sub QFill()
ActiveCell.FormulaR6C17 = "=ROUND(SUMIFS(R6C16:R97C16,R6C8:R97C8,RC8),2)=0"
Range("Q6").Select
Selection.AutoFill Destination:=Range("Q6:Q97")
Range("Q6:Q97").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
How about
Code:
Sub Warpug()
   Dim UsdRws As Long
   UsdRws = Range("P" & Rows.Count).End(xlUp).Row
   Range("Q6:Q" & UsdRws).FormulaR1C1 = "=ROUND(SUMIFS(R6C16:R" & UsdRws & "C16,R6C8:R" & UsdRws & "C8,RC8),2)=0"
End Sub
 

Forum statistics

Threads
1,085,495
Messages
5,384,010
Members
401,873
Latest member
GCPaul

Some videos you may like

This Week's Hot Topics

Top