Convert the formula dynamically to populate the data in Listobject Loops using VBA

Question

I have 6 different Excel tables in a single worksheet with similar column names and I would like to implement 1 formula in one of the columns in all these tables. I would like to populate the Sequence numbers (in the "Sequence" Columns) based on the Property Number repetitions in the "Property Number" Columns.

Following is the formula that I would like to replicate in the Excel tables:

General Formula: =IF(AND(E2=E2),COUNTIFS($E$2:E2,E2),"")

The same formula if I need to replicate it in Tables/Listobjects, looks like this:

=IF(AND([@PropertyNumber]=[@PropertyNumber]), COUNTIFS([@PropertyNumber]:[@PropertyNumber],[@PropertyNumber]),"")

I'm unable to use the Absolute References, but however, I would like to replicate the same Formula in VBA Code to populate the results as shown below:

I have tried to replicate the formula in VBA with the following code and it is throwing error that "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

Please help me populate the sequence numbers based on the Property Numbers available in each table dynamically. Appreciate your help in advance.

What's the point of the IF function given E2=E2 has to be true? — Rory, Jun 27 '23 at 10:10
Probably, there is no impact, but that was the formula that I was using for a very long time and tried to replicate the same. However, now I just need to replicate the counts in the Sequence Column based on the Property Column, it could be any other way also. — mrk777, Jun 27 '23 at 10:20
How do you identify the tables? Are they the only tables in the worksheet or do their names all start with `RealProperty`? What's the long string all about? Is `RealPropertySht` the code name of the worksheet? — VBasic2008, Jun 27 '23 at 10:41
There are names to the Tables based on that I'm going to identify and I have given IF condition to proceed further only if the table name matches, the only thing I need to resolve is the formula that I need to embed in the code which works dynamically for all the other tables as they have the same column names in common. — mrk777, Jun 27 '23 at 11:07

VBasic2008 · Accepted Answer · 2023-06-29T13:15:26.327

Populate Excel Tables with Formulas (`Formula2`) or Values

The ugly layout is used to emphasize that the location of the tables doesn't matter.

Formulas (Microsoft 365)

The 1st code is based on the following formula:
```
=LET(c,[PropertyNumber],v,[@PropertyNumber],
    r,ROW(v)-ROW(RealProperty[#Headers]),
    i,INDEX(c,SEQUENCE(r)),
ROWS(FILTER(i,i=v)))
```
where RealProperty is the table name and PropertyNumber is the title of the column to be 'sequenced'.

Sub UpdateSequenceFormulas()

    Const SRC_TABLES As String = "RealProperty,RentalProperty,TaxableMajor" _
        & "Maintenance,RepairExpenses,SubTable"
    Const COL_NUM As String = "PropertyNumber"
    Const COL_SEQ As String = "Sequence"
    
    Dim ws As Worksheet: Set ws = RealPropertySht
    
    Dim tNames() As String: tNames = Split(SRC_TABLES, ",")
    
    Dim lo As ListObject, srg As Range, Data(), t As Long, Formula As String
        
    For t = 0 To UBound(tNames)
        On Error Resume Next
            Set lo = ws.ListObjects(tNames(t))
        On Error GoTo 0
        If Not lo Is Nothing Then
            Formula = "=LET(c,[" & COL_NUM & "],v,[@" & COL_NUM & "]," _
                & vbLf & "    r,ROW(v)-ROW(" & tNames(t) & "[#Headers])," _
                & vbLf & "    i,INDEX(c,SEQUENCE(r))," _
                & vbLf & "ROWS(FILTER(i,i=v)))"
            'Debug.Print Formula
            Set srg = lo.ListColumns(COL_SEQ).DataBodyRange
            srg.Formula2 = Formula
            Set lo = Nothing
        End If
    Next t
    
    MsgBox "Sequence formulas updated.", vbInformation

End Sub

Values

Sub UpdateSequenceValues()

    Const SRC_TABLES As String = "RealProperty,RentalProperty,TaxableMajor" _
        & "Maintenance,RepairExpenses,SubTable"
    Const COL_NUM As String = "PropertyNumber"
    Const COL_SEQ As String = "Sequence"
    
    Dim ws As Worksheet: Set ws = RealPropertySht
    
    Dim tNames() As String: tNames = Split(SRC_TABLES, ",")
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim lo As ListObject, nrg As Range, srg As Range, Data()
    Dim t As Long, r As Long, rCount As Long, rStr As String
        
    For t = 0 To UBound(tNames)
        On Error Resume Next
            Set lo = ws.ListObjects(tNames(t))
        On Error GoTo 0
        If Not lo Is Nothing Then
            Set nrg = lo.ListColumns(COL_NUM).DataBodyRange
            rCount = nrg.Rows.Count
            If rCount = 1 Then
                ReDim Data(1 To 1, 1 To 1)
            Else
                Data = nrg.Value
            End If
            For r = 1 To rCount
                rStr = CStr(Data(r, 1))
                dict(rStr) = dict(rStr) + 1
                Data(r, 1) = dict(rStr)
            Next r
            Set srg = lo.ListColumns(COL_SEQ).DataBodyRange
            srg.Value = Data
            dict.RemoveAll
            Set lo = Nothing
        End If
    Next t
    
    MsgBox "Sequences values updated.", vbInformation
    
End Sub

Question, @VBasic2008: are you not somewhat replicating the built-in `OFFSET` function in your formula? Is there an advantage over `=LET(r,ROW()-ROW(TableName[#Headers]),COUNTIF(OFFSET(TableName[#Headers],1,0,r,1),[@PropertyNumber]))`? I know for small data sets it's not a big deal. Just curious if it's a question of personal preference or performance. — pdtcaskey, Jun 29 '23 at 12:33
I never use volatile functions if I can help it. I will surely never use `OFFSET`. It is about performance but it is also about flexibility: if you use `OFFSET` you cannot e.g. move (switch) the columns as you please (irrelevant in this case). — VBasic2008, Jun 29 '23 at 12:38
Question2, @VBasic2008: If `dict(rStr)` does not exist, would not `dict(rStr) = dict(rStr) + 1` cause a new row in `dict` with a key of rStr and an empty value, so that `dict(rStr) + 1` would evaluate to 1 and you'd still end up with the `dict(rStr)` value of 1, without the `'if...then` construct? Like prior question, I'm only trying to learn if there are performance or other advantages to different approaches. — pdtcaskey, Jun 29 '23 at 12:45
I certainly see the advantage of avoiding volatile functions; I hadn't considered that performance cost before! Although the issue of moving the column could be compensated (albeit adding complexity and volatility) by defining `c,MATCH(COL_NUM,TableName[#Headers],0)` and adjusting to `OFFSET(TableName[#Headers],1,c-1,r,1)`. — pdtcaskey, Jun 29 '23 at 12:54
@pdtcaskey That was I nice catch (I didn't do a sum dictionary for a long time (a bad excuse, I know)). More importantly, the lines writing to the range should have been outside the loop which was a rather serious mistake (writing `r` times per loop). Fixed it, have a look now. Thanks a bunch. — VBasic2008, Jun 29 '23 at 13:12

score 1 · Answer 2 · answered Jun 29 '23 at 14:54

Another possibility is to create a "named" LAMBDA function; however, it does use "volatile" functions that could bog down recalculations (per @VBasic2008's comments). For small data sets, though, it shouldn't be a problem.

The trick is to define a name for your function (e.g., "ColumnSeq") and set its definition to the LAMBDA =LAMBDA(...). Then you can use =ColumnSeq(...) in your tables.

The LAMBDA would be

=LAMBDA(COL,LET(Base,INDIRECT(CELL("address",COL)),
    Nbr,ROW()-ROW(Base)+1,
    Rng,OFFSET(Base,0,0,Nbr),
    COUNTIF(Rng,INDEX(COL,Nbr,))))

When you invoke ColumnSeq on the entire table column (omit "@", e.g., [PropertyNumber] instead of [@PropertyNumber]), COL in the LAMBDA then represents that entire table column.

The CELL("address",COL) returns the address of the top-left cell of the target range, and the INDIRECT will then "lock" the starting range of the sequence without having to know the table name. Base, then, is the upper left cell of the column.

ROW() returns the row number of each individual row in the column, so subtracting ROW(Base) and adding 1 results in the relative position of each row in the table.

The Rng then uses OFFSET to start at Base and include Nbr rows, resulting in the "first Nbr rows in the column".

The INDEX function, returns the row's value from the column, so COUNTIF operates on "the first Nbr rows" of the column, counting only those that match with the individual row's value... or the sequence of each value within the column.

Again, all that is within the name definition (CTRL + F3). The function in each table can then be =ColumnSeq([ColName]), where "ColName" is the name of the column you're sequencing.

Just tested, and it works outside of a table, too, if the formula is copied for each row of the target range. — pdtcaskey, Jun 29 '23 at 15:04
Just considered, too, that @VBasic2008's formula could likewise be similarly defined as a named `LAMBDA` and avoid the volatility issue. — pdtcaskey, Jun 29 '23 at 15:16

Convert the formula dynamically to populate the data in Listobject Loops using VBA

2 Answers2

Populate Excel Tables with Formulas (Formula2) or Values

Populate Excel Tables with Formulas (`Formula2`) or Values