如何用VBA在Excel多个ListObject中动态生成PropertyNumber对应序列编号

单个工作表中有6个列名相近的Excel表格（ListObject），需要为所有表格的「Sequence」列根据「PropertyNumber」列的重复项生成序列编号：

• 预期实现的公式：
  • 普通单元格公式：=IF(AND(E2=E2),COUNTIFS($E$2:E2,E2),"")
  • 表格结构化引用公式：=IF(AND([@PropertyNumber]=[@PropertyNumber]), COUNTIFS([@PropertyNumber]:[@PropertyNumber],[@PropertyNumber]),"")
• 限制条件：无法使用绝对引用
• 遇到的问题：自行编写的VBA代码报错「property or method not supported」
• 尝试的错误代码：

' Populating the formulas for Sequence Columns in all the RealProperty Tables
Dim RealPropertySequenceCol As Integer
Dim RealPropertyPropertyNumRow1 As Integer
Dim RealPropertyPropertyNumRows As Integer
Dim RealPropertyPropertyCol As Integer
Dim ListObjectTables As ListObject
Dim TblCounts As Integer

For Each ListObjectTables In RealPropertySht.ListObjects
    
    RealPropertyPropertyNumRows = 1
    If ListObjectTables = "RealPropertyRentalPropertyTaxableMajorMaintenanceOrRepairExpensesSubTable" Then
        
        RealPropertyPropertyNumRow1 = ListObjectTables.ListColumns("PropertyNumber").DataBodyRange.Rows(1)
        
        TblCounts = ListObjectTables.DataBodyRange.Rows.Count
        
        If ListObjectTables.ListColumns("PropertyNumber").DataBodyRange.Rows(1).Value = ListObjectTables.ListColumns("PropertyNumber").DataBodyRange.Rows(1).Value Then
            ListObjectTables.ListColumns("Sequence").DataBodyRange.Rows(1).Value = Application.WorksheetFunction.CountIfs(ListObjectTables.ListRows(TblCounts) - (ListObjectTables.ListRows(TblCounts) - ListObjectTables.ListRows(RealPropertyPropertyNumRow1)), RealPropertyPropertyNumRow1, "")
            
        
        End If
    
    
    
    End If

Next ListObjectTables

以下是可以实现需求的VBA代码，核心是利用Excel表格的结构化引用来动态生成序列，完全规避绝对引用的限制：

Sub PopulateSequenceNumbers()
    Dim targetWs As Worksheet
    Dim tbl As ListObject
    Dim propCol As ListColumn
    Dim seqCol As ListColumn
    
    ' 替换为你的目标工作表名称
    Set targetWs = ThisWorkbook.Worksheets("RealPropertySht")
    
    ' 遍历工作表内所有ListObject表格
    For Each tbl In targetWs.ListObjects
        ' 检查表格是否同时包含PropertyNumber和Sequence列
        On Error Resume Next
        Set propCol = tbl.ListColumns("PropertyNumber")
        Set seqCol = tbl.ListColumns("Sequence")
        On Error GoTo 0
        
        ' 仅当两列都存在时执行公式写入
        If Not propCol Is Nothing And Not seqCol Is Nothing Then
            ' 给Sequence列的所有数据行设置结构化引用公式
            seqCol.DataBodyRange.Formula2 = _
                "=IF(COUNTA([@PropertyNumber])>0, COUNTIFS([PropertyNumber]:[@PropertyNumber], [@PropertyNumber]), """")"
        End If
    Next tbl
End Sub

代码要点说明： #

• 使用Formula2而非Formula，适配Excel最新的动态数组和结构化引用语法
• 结构化引用[PropertyNumber]:[@PropertyNumber]会自动生成从该列第一行到当前行的动态范围，完美替代原公式中绝对引用的$E$2:E2
• 增加列存在性检查，避免因表格缺失目标列导致运行报错
• 将原公式中冗余的AND([@PropertyNumber]=[@PropertyNumber])简化为COUNTA([@PropertyNumber])>0，逻辑等价且更高效

内容的提问来源于stack exchange，提问作者mrk777