Excel vba 语法错误公式值

Question

我正在尝试用公式填充表格，但我无法让最后一行炒锅。P列要完成的第一行是11，然后根据要填充的行数(fyrowCount(，我希望它每次增加1。

如果我使用以下公式，它可以工作，但无法找出 vba 中的正确语法。

公式=IFERROR(INDIRECT("'"&[Item ID'#]&"'!$P$" & 11), "")

Application.AutoCorrect.AutoFillFormulasInLists = False
If WorksheetFunction.CountA(Range("Table35[Item ID'#]")) = 0 Then
'Range is empty!
For y = 2 To fyrowCount + 1
Range("A" & y).Select
Z = 9 + y
Cells(ActiveCell.Row, 1) = wsName
Cells(ActiveCell.Row, 2).Value = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$7""), """")"
Cells(ActiveCell.Row, 3).Value = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$6""), """")"
Cells(ActiveCell.Row, 4).Value = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$8""), """")"
Cells(ActiveCell.Row, 5).Value = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$9""), """")"
Cells(ActiveCell.Row, 6).Value = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$P$"" & z), """")"
Next y
End If

Answer 1

将Value换成Formula，如下所示：Cells(ActiveCell.Row, 2).Formula =

---

另外，您的Range或Cells对象都不是使用工作表限定的。这可能会有问题。我强烈建议明确说明这些对象的位置。(Sheets("x").Cells(y, 1) = wsName(。

此外，您不需要在此处.Select单元格，如下所示。

Application.AutoCorrect.AutoFillFormulasInLists = False
If WorksheetFunction.CountA(Range("Table35[Item ID'#]")) = 0 Then
For y = 2 To fyrowCount + 1
Z = 9 + y
Cells(y, 1) = wsName
Cells(y, 2).Formula = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$7""), """")"
Cells(y, 3).Formula = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$6""), """")"
Cells(y, 4).Formula = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$8""), """")"
Cells(y, 5).Formula = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$G$9""), """")"
Cells(y, 6).Formula = "=IFERROR(INDIRECT(""'""&[Item ID'#]&""'!$P$"" & z "), """")"
Next y
End If