Excel VBA在表中更改我的公式？

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

有没有人遇到Excel似乎操纵你的公式的情况。我有一张表格,其中我在列A中有一个索引值。第一行以任何非零值开头。列中的后续行会增加该值。例如

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:

/等等/我有另一列B,其值为空白或指向列A的公式(通常是后续行)例如:

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.

现在我有一个备份恢复功能,可以让我将数据/公式写出来一个文本文件,然后在另一个工作簿中读回来。在列A和B的情况下,我正在检查文本值是否以“=”开头,然后根据是否存在公式来设置该单元格的值或公式。

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:

现在,如果我将此数据范围转换为表并相应地修改代码,则行为很奇怪。我使用ListObject结构来引用该表。因此,对于B列,我的恢复代码是:

 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?

当我使用表而不是范围时,为什么我的公式值都变为相同的值?

2 个解决方案

#1

I faced a similar issue. Ideally you could tell excel to stop doing this but I haven't been able to figure out how. Supposedly doing the following is supposed to keep excel from copying the formulas:

我遇到了类似的问题。理想情况下,你可以告诉excel停止这样做,但我无法弄清楚如何。据说执行以下操作应该保持擅长复制公式:

xlApp.AutoCorrect.AutoFillFormulasInLists = false

xlApp.AutoCorrect.AutoFillFormulasInLists = false

but it didn't work for me.

但它对我不起作用。

Using the answer from this question How to create running total using Excel table structured references? helped me. It doesn't feel like the ideal solution but it does do the job.

使用此问题的答案如何使用Excel表结构化引用创建运行总计?帮助过我。它不是理想的解决方案,但它确实能够完成这项工作。

I used this formula where Weight is a column name from my table. #This Row is a "Special item specifier" and has a special meaning. The syntax looks a little funky because it's what's called a Structured Reference:

我使用这个公式,其中Weight是我表中的列名。 #This Row是一个“特殊项目说明符”,具有特殊含义。语法看起来有点时髦,因为它就是所谓的结构化参考:

=AVERAGE(INDEX([Weight],1):[[#This Row],[Weight]])

The INDEX([Weight],1) part gives the reference for the 1st row in the Weight column

INDEX([Weight],1)部分给出Weight列中第1行的参考

While the [[#This Row],[Weight]] part gives the reference for the current row in the Weight column.

虽然[[#This Row],[Weight]]部分给出了Weight列中当前行的参考。

So for example, if Weight is column J, and the current row is, say, 7 then this is equivalent to

因此,例如,如果Weight是列J,而当前行是7,那么这相当于

=AVERAGE(J1:J7)

and on the 8th row it will be equivalent to

在第8行,它将相当于

=AVERAGE(J1:J8) and so on

= AVERAGE(J1:J8)等

#2

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

从Excel加载项填充ListObject(表)时遇到了同样的问题,设置AutoFillFormulasInLists是解决方案。

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.

我的解决方法是保存当前设置,将AutoFillFormulasInLists设置为false,用数据,公式等填充表格,然后将AutoFillFormulasInLists设置回原始设置。

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

//[ListObject population code....]

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

Hope this helps someone.

希望这有助于某人。

#1