Dynamic reference to Table in Excel VBA

Question

I am trying to count dynamic in a table using the header (see the picture below). The problem is that i dont know how to make Tabelle14[[#Headers],[Cinema]] dynamic. I tried it with R[-1]C[0] but this is not the solution.

Is where any way to do it without a loop or do you have a better idea how to solve it?

At the moment i am doing this with .FormulaR1C1

Here is my Code

Sub countit()

Dim i As Integer: i = 2
Dim n_col As Integer

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Tabelle14")

n_col = tbl.HeaderRowRange.Columns.Count - 1

Do While i < n_col

ActiveSheet.ListObjects("Tabelle14").ListColumns(i - 1).DataBodyRange(1, i).Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(Tabelle13[Date],[@Date],Tabelle13[Name],Tabelle14[[#Headers],[Cinema]])"

i = i + 1
Loop

VBasic2008 · Accepted Answer · 2021-07-09 12:17:12Z

Populate CountIfs Table

Adjust the values in the constants section.

Option Explicit

Sub PopulateCountIfsTable()
    
    ' Source
    Const sName As String = "Sheet1"
    Const stName As String = "Tabelle13"
    
    ' Destination
    Const dName As String = "Sheet1"
    Const dtName As String = "Tabelle14"
    Const dfCol As Long = 2
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim stbl As ListObject: Set stbl = sws.ListObjects(stName)
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dtbl As ListObject: Set dtbl = dws.ListObjects(dtName)
    Dim dlCol As Long: dlCol = dtbl.Range.Columns.Count
    
    Dim c As Long
    Dim cFormula As String
    
    For c = dfCol To dlCol
        
        cFormula = "=COUNTIFS(" & stName & "[" & stbl.HeaderRowRange(1).Value _
            & "],[@" & dtbl.HeaderRowRange(1).Value & "]," & stName & "[" _
            & stbl.HeaderRowRange(2).Value & "]," & dtName & "[[#Headers],[" _
            & dtbl.HeaderRowRange(c).Value & "]])"
        
        dtbl.ListColumns(c).DataBodyRange.Formula = cFormula
    
    Next c

End Sub

Collectives™ on Stack Overflow

Dynamic reference to Table in Excel VBA

1 Answer 1

Populate CountIfs Table

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Populate CountIfs Table

Comments

Your Answer

Sign up or log in

Post as a guest

Related