Add / Modify / Delete Computed Column Formula in Excel Listobject / Table via VBA

Question

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

Is there a way to control this behavior through VBA, i.e. modify / delete / add this formula in any way?

I know that I can just change the formula of the ListObject.ListColumns(1).DataBodyRange - but it will overwrite any previously entered values ​​manually - when you change the formula in the user interface, it will remain untouched ...

Answer 1

Thanks to the comments and answers of Doug and bonCodigos, I found a simple answer:

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

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

Answer 2

It would be best if you could show us a screenshot on your sheet. Based on this, we would formulate the answer.

Here with a general assumption. So that you have one list object to dump some data into a column, and the rest of the cells in this column interact manually.

First, you can try the following manually and see if it works for you. However, the handheld device is overtaken by the code level, then you do it at the code level. :)

The main action here is to stop automatic fixes.

Go to → "Tools" menu → "AutoCorrect Settings" → On the AutoCorrect tab →

1- To prevent ALL automatic corrections

Uncheck Replace Text as you type

2- To prevent SPECIFIC correction

Clear the corresponding check box for the option. I believe that you are more interested in the last range of data that you just dump with listobject.

---

Now here is the code to disable this feature at the code level.

When working with Excel tables (ListObjects), there are two AutoCorrect options: you can read this data in detail.

 * Apply new rows and columns in table (VBA AutoCorrect.AutoExpandListRange Property) * Fill formulas in tables to create calculated columns (VBA AutoCorrect.AutoFillFormulasInLists Property) 

The code you can use at the top of our list is

 Application.AutoCorrect.AutoFillFormulasInLists = False 

And I completely agree that it would be much more useful if Application.AutoCorrect. AutoFillFormulasInLists Application.AutoCorrect. AutoFillFormulasInLists controlled by a table by table at the ListObject level. So here is a workaround.

Thus, one way is to clear the table data each time. And when you clear the data, you can make sure that TABLE FORGET forumulae and formatting. Therefore, before deleting the contents of the table, it will clear the contents of the range of the data body.

 Sub forgetMe() With Sheet1.ListObjects("myTable") If Not .DataBodyRange Is Nothing Then .DataBodyRange.ClearContents .DataBodyRange.Delete End If End With End Sub 

When you enter data, start by automatically deleting files.