Add/Modify/delete calculated column formula in Excel Listobject/Table via VBA

excel listobject excel-tables vba
Peter Albert picture Peter Albert · Dec 6, 2012 · Viewed 36.6k times · Source

If I manually enter a formula into a column in an Excel table (i.e. ListObject), AutoCorrect applies this formula to the whole column.

Is there any way to control the this behavior via VBA, i.e. can I somehow modify/delete/add this formula?

I know I can simply change the formula of the ListObject.ListColumns(1).DataBodyRange object - but this will overwrite any manually values entered before - while changing the formula in the UI will leave this untouched...

Answer

Peter Albert picture Peter Albert · Dec 7, 2012

Thanks to Doug's and bonCodigos comments/answers, I found the simple answer:

ListObject.ListColumns("Column name").DataBodyRange.FormulaR1C1 = "new formula"

This will overwrite any manual value (just as the normal behavior with AutoCorrect).

Related questions

How do I reference tables in Excel using VBA?

Is it possible in Excel VBA to reference a named table? Hypothetically this could be... Sheets("Sheet1").Table("A_Table").Select I have seen some mention of tables being a list object but I'm not sure if that is the …

Read More
Create Table in Excel Worksheet using VBA

I have this code below that will auto select a range. Does anyone know how I can add code to create a table to the selected range? Thanks! Sub DynamicRange() 'Best used when first column has value on last row …

Read More
What is the proper way to insert values into an excel table using VBA?

I need to be able to insert a large set of values into an empty table in excel using VBA code. Here's how the code works so far. First, the user inputs a value into a userform. The code then …

Read More