excel - Excel VBA中对表的动态引用

Question

我正在尝试使用标题在表格中计算动态（见下图）。问题是我不知道如何使Tabelle14[[#Headers],[Cinema]]动态。我用 R[-1]C[0] 试过，但这不是解决方案。

有什么方法可以在没有循环的情况下做到这一点，或者您有更好的解决方法吗？

目前我正在这样做.FormulaR1C1

这是我的代码

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

标签： excelvba

Answer 1

填充 CountIfs 表

• 调整常量部分中的值。

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