Excel VBA 更改表格中的公式?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/17102839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA changing my formulas in a table?
提问by DPD
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?
当我使用表格而不是范围时,为什么我的公式值都更改为相同的值?
回答by Andy Sinclair
I had the same issue when populating a ListObject (Table) from an Excel Add-in, setting AutoFillFormulasInLists
was the solution.
从 Excel 加载项填充 ListObject (Table) 时,我遇到了同样的问题,设置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.
希望这可以帮助某人。
回答by DickM
I have found that the only way to solve the problem of formulas changing in Excel Tables when you insert in VBA is to insert at the first row of the table, NOT the bottom or the middle. You can sort after.
我发现在 VBA 中插入时解决 Excel 表格中公式更改问题的唯一方法是插入表格的第一行,而不是底部或中间。你可以排序。
And I always select or reference the EntireRow
to do my insert in the Worksheet object not in the table itself. I always put a table in its own Worksheet anyway using xx.Entirerow.Insert
.
而且我总是选择或引用EntireRow
在 Worksheet 对象中而不是在表本身中进行插入。无论如何,我总是使用xx.Entirerow.Insert
.
回答by User
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 停止这样做,但我一直无法弄清楚如何。据说执行以下操作应该可以防止 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]])
=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)
部件给出了重量列中第一行的参考
While the [[#This Row],[Weight]]
part gives the reference for the current row in the Weight column.
而[[#This Row],[Weight]]
零件在权重列中提供当前行的参考。
So for example, if Weight is column J, and the current row is, say, 7 then this is equivalent to
例如,如果权重是 J 列,而当前行是 7,那么这相当于
=AVERAGE(J1:J7)
=AVERAGE(J1:J7)
and on the 8th row it will be equivalent to
在第 8 行,它将相当于
=AVERAGE(J1:J8)
and so on
=AVERAGE(J1:J8)
等等