All,
I have a tricky VBA Code Scenario.
I'm trying to define a table range then loop through all columns in said table range and change the formula in the total row of the table.
The Tricky Part is my Table Self Updates so the number of columns and the label of the columns change depending on the project.
Step one: Define range starting in Column 10 of table and including all remaining columns.
Step Two: For each columns in range select Total Row and paste Formula. Formula needs to include column Header value so we will need to define this as a variable.
Defining what to do with the columns is where I'm lost.
Current Code:
I have a tricky VBA Code Scenario.
I'm trying to define a table range then loop through all columns in said table range and change the formula in the total row of the table.
The Tricky Part is my Table Self Updates so the number of columns and the label of the columns change depending on the project.
Step one: Define range starting in Column 10 of table and including all remaining columns.
Step Two: For each columns in range select Total Row and paste Formula. Formula needs to include column Header value so we will need to define this as a variable.
Defining what to do with the columns is where I'm lost.
Current Code:
VBA Code:
Sub Macro3()
Dim oSheetName As Worksheet
Dim sTableName As String
Dim loTable As ListObject
'Define Variable
sTableName = "Table_Earned31"
'Define WorkSheet object
Set oSheetName = Sheets("Project Header")
'Define Table Object
Set loTable = oSheetName.ListObjects(sTableName)
loTable.ListColumns(10).DataBodyRange.Resize(, ActiveSheet.ListObjects("Table_Earned31").HeaderRowRange.Columns.Count - 9).Select
Dim rng As Range: Set rng = loTable.ListColumns(10).DataBodyRange.Resize(, ActiveSheet.ListObjects("Table_Earned31").HeaderRowRange.Columns.Count - 9)
Dim col As Range
For Each col In rng.Columns
'Define what to do with each column
Variable = HeadersRowRange
TotalsRowRange.Select
ActiveCell.FormulaR1C1 = _
"=SUMIF([UNIT],Table_Earned31[[#Totals],[UNIT]]," & Variable & ")"
Next col