Dynamic sumif formula in VBA referencing to a table

G

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
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.
 

Attachments

  • Sumifs from Table.xlsm
    19.3 KB · Views: 20
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.
 

Attachments

  • Sumifs from Table.xlsx
    21.8 KB · Views: 17
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.
 
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
 
That worked great!. Thanks! I will study this procedure and hopefully learn from it.
 
You must log in or register to reply here.
Back
Top