使用 VBA 生成的公式在工作表之间进行引用
我正在尝试使用引用当前工作表和另一个工作表的基本公式填充单元格。
我尝试输入单元格的公式基本上是... ='Sheet2'A(j) / B(i) ，代码如下：
For i = 1 To 5
For j = 1 To 5
With shOut.Cells(i,j)
.formula = "='Sheet2'" & Cells(j,1)).Address & "/" & Cells(i,2).Address
End With
Next j
Next i
我遇到问题的部分是工作表在开始时引用；当我在 .formula 中运行它时，它会触发错误。
但是，当我删除“=”符号并将其输出到 .Value 中时，它会打印出我想要的内容，但它不是公式。
I'm trying to populate a cell with a basic formula that references both the current worksheet and another worksheet.
The formula I am trying to input into a cell is basically... ='Sheet2'A(j) / B(i) with the following code:
For i = 1 To 5
For j = 1 To 5
With shOut.Cells(i,j)
.formula = "='Sheet2'" & Cells(j,1)).Address & "/" & Cells(i,2).Address
End With
Next j
Next i
The part I am having trouble with is the sheet referencing at the start; when I run this in a .formula, it triggers an error.
However, when I remove the '=' sign and make it output in a .Value, it prints out what I want it to, except it's not a formula.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不必自己构建任何范围的地址 - 这样做只会让自己面临潜在的问题。
例如，在 ularis（非常好的）答案中，工作表名称周围没有单引号。在这种情况下，它们不是必需的，但如果工作表名称中包含空格，那么您确实需要引号。 （我的观点是，您不必自己了解并满足所有这些内容）。
相反，您可以在对
Address()的调用中指定External:=True，这将为您提供一个包含工作表名称的地址。我会做这样的事情：请注意，在您的情况下，您可能只需要对其中一个工作表使用
External参数，但这样做额外的时间不会有什么坏处，因为 Excel 会简化无论如何，这个公式适合你。You shouldn't have to construct the address of any range yourself - you'll only leave yourself open to potential problems by doing so.
For example, in cularis' (perfectly good) answer, there are no single-quotes around the sheet name. They're not required in this case, but if the sheet name had a space in it, then you do need the quotes. (My point is that you shouldn't have to know - and cater for - all this stuff yourself).
Instead, you can specify
External:=Truein the call toAddress(), which will give you an address that includes the sheet name. I'd do something like this:Note that in your case you probably only need to use the
Externalparameter for one of the worksheets, but doing it that extra time won't hurt, as Excel will simplify the formula for you anyway..formula开头的行中)过多。工作表后面缺少
!：)too much in your line that begins with.formula.You are missing the
!after the sheet: