I am trying to write this formula using VBA:
我正在尝试使用VBA编写此公式：
ActiveCell.Value = "=f(R[-1]C[0],Sheet1!" & ColumnLetter & i & ")"
Where ColumnLetter is some variable letter which my macro computes earlier, and f is some function, and i is some number.
其中ColumnLetter是我的宏之前计算的一些变量字母，f是某个函数，而我是一些数字。
The problem is that when I run this, the cell is given this instead: (if ColumnLetter = F, i = 16):
问题是，当我运行它时，相反给出了单元格:(如果ColumnLetter = F，i = 16）：
=f(R[-1]C[0],Sheet1!'F16')
but I want:
但我想要：
=f(R[-1]C[0],Sheet1!F16)
Why is VBA or Excel putting those single quotation marks around F16? It does not insert these extra quotation marks if I do not include R[-1][0] as an argument in my formula, but I need to include this.
为什么VBA或Excel会在F16附近放置这些单引号？如果我在公式中不包含R [-1] [0]作为参数，它不会插入这些额外的引号，但我需要包含它。
Help much appreciated!
非常感谢！
2 个解决方案
#1
9
Its the combination of R1C1 and A1 addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16) into a cell you will get an error for the same reason.
它是R1C1和A1寻址的组合。您需要选择一种方法并将其用于两个部分。请注意，如果您将= f（R [-1] C [0]，Sheet1！F16）键入单元格，则会出于同样的原因出错。
You say you need to use R1C1 style for the first address, but (assuming this is because you don't want absolute address) you can use .Offset instead
你说你需要使用R1C1样式作为第一个地址，但是（假设这是因为你不想要绝对地址）你可以使用.Offset代替
ActiveCell.Value = "=f(" & ActiveCell.Offset(-1, 0).Address(False, False) _
& ",Sheet1!" & ColumnLetter & i & ")"
#2
1
The Apostrophe means to Excel that it should interpret it as text.
Apostrophe意味着它应该将它解释为文本。
Write it to ActiveCell.Formula. That way it is recognized as Formula.
将它写入ActiveCell.Formula。这样它被认为是公式。
#1
9
Its the combination of R1C1 and A1 addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16) into a cell you will get an error for the same reason.
它是R1C1和A1寻址的组合。您需要选择一种方法并将其用于两个部分。请注意，如果您将= f（R [-1] C [0]，Sheet1！F16）键入单元格，则会出于同样的原因出错。
You say you need to use R1C1 style for the first address, but (assuming this is because you don't want absolute address) you can use .Offset instead
你说你需要使用R1C1样式作为第一个地址，但是（假设这是因为你不想要绝对地址）你可以使用.Offset代替
ActiveCell.Value = "=f(" & ActiveCell.Offset(-1, 0).Address(False, False) _
& ",Sheet1!" & ColumnLetter & i & ")"
#2
1
The Apostrophe means to Excel that it should interpret it as text.
Apostrophe意味着它应该将它解释为文本。
Write it to ActiveCell.Formula. That way it is recognized as Formula.
将它写入ActiveCell.Formula。这样它被认为是公式。