Excel VBA changing my formulas in a table?

Question

Has anyone come across a situation where Excel seems to manipulate your formulas. I have a sheet where I have an Index value in Column A. The First row starts with any non zero Value. Subsequent rows in the column increment the value. Eg

A1 = 1000
A2= A1+ 1
A3= A2 + 1 

and so on/ I have another column B whose values will either be blank or a formula pointing to column A(usually the subsequent rows) Eg:

B1.Formula = "=A2"
B2.Formula = "=A3"
B3.Value = ""
B4.value = "=A6"

Now I have a backup-restore functionality that lets me write out the data/formulas to a text file and then read it back in another workbook. In the case of columns A and B, I am checking if the text value starts with "=" and then set either the value or formula of that cell depending on whether there is a formula or not.

So far the functionality has worked fine. It lets me restore accurately.

Now, if I convert this data range to a table and modify the code accordingly the behaviour is strange. I am using the ListObject structure to refer to the table. So for Column B my restore code is:

 If Left(soureString) = "=" Then
               'This is a formula
               Sheets("MySheet").ListObjects(1).ListColumns("Next").DataBodyRange(row).Formula = sourcestring
Else
               'This is a value
                Sheets("MySheet").ListObjects(1).ListColumns("Next").DataBodyRange(row).Value = soureString
End If

once I am done writing a row, I loop to the start and

Dim newRow AS listrow
Set newRow = Sheets("MySheet").Listrows.Add(AlwaysInsert:=False)
row = newRow.Index

But this time when I run the process. this is what I get:

B1.Formula = "=A5"
B2.Formula = "=A5"
B3.Value = ""
B4.value = "=A5"

Why are my formula values all changing to the same value when I use a table instead of a range?

Answer 1

I had the same issue when populating a ListObject (Table) from an Excel Add-in, setting AutoFillFormulasInLists was the solution.

My workaround is to save the current setting, set AutoFillFormulasInLists to false, populate the table with data, formulas etc, then set AutoFillFormulasInLists back to the original setting.

bool OriginalAutoFillFormulaInListsFlag = app.AutoCorrect.AutoFillFormulasInLists;
app.AutoCorrect.AutoFillFormulasInLists = false;

//[ListObject population code....]

if (OriginalAutoFillFormulaInListsFlag == true)
{
    app.AutoCorrect.AutoFillFormulasInLists = true;
}

Hope this helps someone.