Updating table names dynamically in formulas in VBA

Question

Update: Thank you all - Having the spaces before and after the ampersands in the CountA solved the problem in one location, and being able to use the Row(Table) function suggested in the comments worked as well! Best community ever (I've been stalking this site to get ideas for about 9 months. This was my first post! It's been over 20 years since I coded, so you guys TOTALLY rock!

I have a macro that separates data into separate sheets by a category. It then turns the data on each sheet into a table with a different name for each sheet. I've used this code to do that:

nLastRowNewSheet = ActiveSheet.Range("B" & objWorksheet.Rows.Count).End(xlUp).Row
            ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$" & nLastRowNewSheet), , xlYes).Name = _
            "Table" & index
            ActiveSheet.ListObjects("Table" & index).TableStyle = "TableStyleLight12"

That part works great. The issue I'm having is that, when I try to create a macro to insert a formula to count the items in this list for a summary at the top of each sheet, I can't get the table number to automatically update the table number. I started by recording a macro that I'd planned up updating to make more dynamic.

ActiveCell.FormulaR1C1 = "=COUNTA(Table0[ID '#])"

I can't figure out how to change the 0 on the table reference to a dynamic value based on the index I've already used.

I tried the "table" & index like I used to create tables with different names,

   ActiveCell.FormulaR1C1 = "=COUNTA(Table"&index&"[ID '#])"

but I get a compile error: Expected End of Statement pop-up box.

Any suggestions on how to get that table number to update so that when the data is sorted if the stakeholders add or remove items from their tab the totals update.

Much thanks for any suggestions.

Try adding spaces before and after & (eg "=COUNTA(Table" & index & "[ID '#])" — Shrotter
– Shrotter, Commented May 14 at 11:37
ActiveCell.FormulaR1C1 = Replace("=COUNTA(Table0[ID '#])","0",index) — CDP1802
– CDP1802, Commented May 14 at 12:17
@Ike I would guess the column name is ID # the ' is to escape the hash. — CDP1802
– CDP1802, Commented May 14 at 14:14
When used without a leading space in VBA & acts as a "type hint" indicating to the compiler that the preceding text represents a variable of type Long. Adding a leading space before the & removes the confusion. — Tim Williams
– Tim Williams, Commented May 14 at 15:18

rotabor · Accepted Answer · 2025-05-14 14:34:22Z

1

Just use the ListObject property to get the table:

ActiveCell.FormulaR1C1 = "=COUNTA(" & [A1].ListObject.Name & "[ID '#])"

where A1 is any cell within the table.

answered May 14 at 14:34

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ike · Accepted Answer · 2025-05-14 14:20:12Z

0

If there is a single quote in the columns name (ID '#) then you have to escape it.

This should work:

ActiveCell.FormulaR1C1 = "=COUNTA(Table" & index & "[ID '''#])"

If there is only the # in the columns name (ID #) then it looks like this:

ActiveCell.FormulaR1C1 = "=COUNTA(Table" & index & "[ID '#])"

edited May 14 at 14:20

answered May 14 at 12:47

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Collectives™ on Stack Overflow

Updating table names dynamically in formulas in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related