Dynamic calculations by referencing table header for Excel

Question

I'm quite new to the Macro function in Excel.

I'm working with a table with a dynamic number of columns, and while some of the calculations I can do manually in Excel, I'm trying to figure out how to automate this in VBA.

The number of columns between column G (Achieved) and P (Total % Class) may differ, but the calculations will be about the same.

I am hoping to use the Table[Header] to specify where to calculate, perhaps whichever column is on the right of 'Achieved' and left of 'Total % Class'

Total % Class:
= SUM all classes except Unclassed

Total % at Higher Class:
= SUM Class_1, Class_2, Class_2F and Class_2P together

Some of these Classes won't always appear in the data, if any one these are missing it will return a #REF error for 'Total % Class' and 'Total % at Higher Class', so I'm hoping to bypass this.

Here's a screengrab of the data, showing all the classes.

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2019-02-03T23:31:54

This macro assumes your data is an Excel Structured Table (if it's not, backup your workbook and consider selecting the data and pressing "Ctrl" + "T" to turn it into one)

Customize the section inside the code:

Sub DoCalcs()

    ' Declare objects variables
    Dim excelTable As ListObject

    ' Declare other variables
    Dim sheetName As String
    Dim excelTableName As String

    Dim achievedColHeader As String
    Dim achievedColNum As Integer
    Dim calculationColHeader As String
    Dim calculationColNum As Integer
    Dim firstColHeader As String
    Dim lastColHeader As String

    '>>>>>>>>Customize to fit your needs
    sheetName = "Sheet1"
    excelTableName = "Table1"

    achievedColHeader = "Achieved"
    calculationColHeader = "Total % Class"
    '<<<<<<<<

    ' Initialize the listobject
    Set excelTable = ThisWorkbook.Worksheets(sheetName).ListObjects(excelTableName)

    ' Get the column number of the first column
    achievedColNum = excelTable.HeaderRowRange.Find(achievedColHeader).Column

    ' Get the column number of the calculation column
    calculationColNum = excelTable.HeaderRowRange.Find(calculationColHeader).Column

    ' Get first and last columns headers of sum's range
    firstColHeader = excelTable.HeaderRowRange.Columns(achievedColNum + 1)
    lastColHeader = excelTable.HeaderRowRange.Columns(calculationColNum - 1)

    ' Set the calculation formula in cell
    excelTable.DataBodyRange.Columns(calculationColNum).Formula = "=SUM(Table1[@[" & firstColHeader & "]:[" & lastColHeader & "]])"


End Sub

Dynamic calculations by referencing table header for Excel

1 Answers