Autofill Formula Across Table with Variable Number of Columns & Rows
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Autofill Formula Across Table with Variable Number of Columns & Rows

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Autofill Formula Across Table with Variable Number of Columns & Rows

     
    I'm trying to create a Macro in VBA that will autofill a formula in the first column of the total row of a table over to a newly created column all the way to the right. Here's what I have right now:

    Week Person #1 Person #2
    #1
    #2
    #3
    #4
    #5
    #6
    #7
    #8
    =IFERROR(ROUND(SUM(LOOKUP(2,1/([Week]<>""),[Week]))-A2,1),"")

    Code:
    Sub Add_Person()
        Dim ws As Worksheet
        Set ws = ActiveSheet
        Dim tbl As ListObject
        Set tbl = ws.ListObjects("Weight")
        tbl.ListColumns.Add.Name = "New Person"
        Range("Weight[[#Totals],[Week]]").Select
        Selection.AutoFill Destination:=Range("Weight[#Totals]"), Type:= _
            xlFillDefault
        Worksheets("Weight Log").Cells.EntireColumn.AutoFit
    End Sub
    The issue I'm running into is that the formula in the first column of the total row of my table isn't changing to reference the new column it's being copied over to. The first column is titled "Week", the second column "Person #1 ", and the third column is "Person #2 ". As it autofills from the first column, the formula should change to reference the column titled "Person #1 ", "Person #2 ", etc.

    To add another wrinkle into this, the total row is subject to change as new rows will get inserted as time goes on.

    Thank you in advance for your help.

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofill Formula Across Table with Variable Number of Columns & Rows

      
    Have you tried Range.FormulaR1C1 instead of the Range.AutoFill?

    In my limited experience with AutoFill in VBA, relative referencing works. If I had to take a shot in the dark, I'd say the named ranges might be causing issues.

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
  •  

 

 
DMCA.com