How to Auto FIll formula with dynamic range
Results 1 to 2 of 2

Thread: How to Auto FIll formula with dynamic range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to Auto FIll formula with dynamic range

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How to Auto FIll formula with dynamic range

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •