Hey, I am trying to create a dynamic table in excel using VBA based on some primary fields:
I'd like to setup some VBA code to:
1) Find the max number of timepoint in column "Total Timepoints" & define as "ColNum"
2) Create New Columns (Based on ColNum +1)
3) Name New Columns by Day 0, Day 7, Day 14, Day 21...Day 56
4) Populate rows of new columns with "Quantity" specified for that row, but only up to that rows Total Timepoints +1. For example the 2nd row in the above table should have "5" for each cell from new columns created (Day 0, Day 7, Day 14, Day 21, Day 28); Row 3 should have quantity "10" for each cell from new columns (Day 0, Day 7,...,Day 56)
So far, I have the below, but I have no idea what I'm doing so any help would be really appreciated:
Quantity | Frequency | StartDate | EndDate | Total Timepoints | New Column 1 | .... | .... | New Column 8 |
5 | Weekly | 04/15/2021 | 05/13/2021 | =If(Frequency = "weekly", Days(EndDate,StartDate)/7,,) | ||||
10 | Weekly | 04/15/2021 | 06/10/2021 | =If(Frequency = "weekly", Days(EndDate,StartDate)/7,,) |
I'd like to setup some VBA code to:
1) Find the max number of timepoint in column "Total Timepoints" & define as "ColNum"
2) Create New Columns (Based on ColNum +1)
3) Name New Columns by Day 0, Day 7, Day 14, Day 21...Day 56
4) Populate rows of new columns with "Quantity" specified for that row, but only up to that rows Total Timepoints +1. For example the 2nd row in the above table should have "5" for each cell from new columns created (Day 0, Day 7, Day 14, Day 21, Day 28); Row 3 should have quantity "10" for each cell from new columns (Day 0, Day 7,...,Day 56)
So far, I have the below, but I have no idea what I'm doing so any help would be really appreciated:
VBA Code:
Sub AutoAddColumns()
Dim Table As ListObject
Dim NumOfColumns As Integer
Dim iCnt As Integer
Dim h As Long, hdrs As Variant
Dim Rows As Integer
hdrs = Array("Day 0", "Day 7", "Day 14", "Day 21", "Day 28", "Day 35", "Day 42", "Day 49", "Day 56", "Day 63", "Day 70", "Day 77", "Day 84", "Day 91", "Day 98", "Day 105", "Day 112", "Day 119", "Day 126", "Day 133", "Day 140", "Day 147")
Set Table = Worksheets("Sheet1").ListObjects("TableName")
NumOfColumns = Application.WorksheetFunction.Max(Range("TableName[Total Timepoints]"))
For iCnt = 0 To NumOfColumns
Table.ListColumns.Add
Table.ListColumns(Table.ListColumns.Count).Name = hdrs(iCnt)
Next
End Sub