Results 1 to 5 of 5

Thread: Dynamic sumif formula in VBA referencing to a table

  1. #1
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365

    Dynamic sumif formula in VBA referencing to a table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a attached a file with a mock sales data type table in it.

    I want to create a VBA procedure that fills in a grid that looks at row and column headings and picks the correct information from the table.

    I've started my code so that it fills in a value in each part of my grid using nested For routines. I've hard coded my Sumifs variables so that it has picked one set of conditions to enter into all cells.
    What I want is to look at the values in columns A and B as well as the header in row 4 and amend the formula accordingly.

    For instance in cell D9 the formula should adapt so that it inputs the value of 5 because...
    it is referencing Table2[Units] rather than Table2[Calls]
    it is referencing Morgan rather than Drew
    it is referencing Montreal rather than New York



    I have no idea as to what syntax to use for such a thing but I'm hopeful it's possible.

    My actual workbook has lots of proprietary info in it and will ultimately reference more columns and more left side variables but presumably this simpler table could be adapted to something more complex. There would also be many rows that would add into the Sumifs. my example only shows one instance per variable set for simplicity.

    Thanks again for any help.
    Attached Files Attached Files

  2. #2
    Neophyte spillerbd's Avatar
    Join Date
    Apr 2016
    Location
    Collierville, TN
    Posts
    3
    Articles
    0
    Excel Version
    Office365, Office 2016
    A VBA procedure seems like a lot work when you can accomplish with a Pivot Table.
    If you want to go with a formula, the raw formula is in the attached for your adaption to VBA.
    Attached Files Attached Files

  3. #3
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    The file in question will eventually be quite large and I want to cut down calculation time and file size. I don't want the actual formula in the spreadsheet to recalculate whenever there is an entry. Also from what I've read, pivot tables can cause your file size to grow. If I segregate this calculation in the background for only when I want it, I can manage my spreadsheet better.

    The current version of the spreadsheet (which I am redesigning) is a memory pig. I'd like to streamline it with VBA.

    Also I am self learning VBA so even if for academic purposes, I'd like to learn various syntaxes for different things to improve my skill set.

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    I manually put the SumIfs formula in C5, dragged it across and then dragged it down.
    VBA wise it's using autofill along the lines of this
    Code:
    Sub AutoFillWithFormulas()
    
    Dim lr As Long, lc As Long
    
    With Sheets("Output")
        ' range extents, starting point is C5
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row             'last row column A
        lc = .Cells(4, .Columns.Count).End(xlToLeft).Column     'last col header row
        ' insert formulas
        .Range("C5").Formula = "=SUMIFS(Table2[Calls],Table2[City],A5,Table2[Name],B5)"
        .Range("C5").AutoFill Destination:=.Range(.Cells(5, 3), .Cells(5, lc))
        .Range(.Cells(5, 3), .Cells(5, lc)).AutoFill Destination:=.Range(.Cells(5, 3), .Cells(lr, lc))
        ' formulas to values
        .Range(.Cells(5, 3), .Cells(lr, lc)).Value = .Range(.Cells(5, 3), .Cells(lr, lc)).Value
    End With
    
    End Sub

  5. #5
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    That worked great!. Thanks! I will study this procedure and hopefully learn from it.

Posting Permissions

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