vba 使用公式R1C1中的变量进行偏移
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18992234/
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
using variable inside formulaR1C1 for offseting
提问by user2812791
I have the following line of code:
我有以下代码行：
ActiveCell.FormulaR1C1 = "=sqrt(RC[-1])"
Now instead of -1, I want to use a variable, say x, as follows:
现在代替 -1，我想使用一个变量，比如 x，如下所示：
ActiveCell.FormulaR1C1 = "=sqrt(RC[x])"
This returns an error. Is there any way I can do this?
这将返回一个错误。有什么办法可以做到这一点吗？
回答by RBarryYoung
Like this:
像这样：
ActiveCell.FormulaR1C1 = "=sqrt(RC[" & x & "])"
回答by Sathish Kothandam
Already RBarryYoung given the right solution
RBarryYoung 已经给出了正确的解决方案
May be you could try the below code
也许你可以试试下面的代码
Tested
已测试
Sub Macro1()
Dim X As Integer
X = 1
Range("C1").Value = Sqr(Range("B" & X).Value)
End Sub
Change the code as per your needs
根据您的需要更改代码
回答by Dan Donoghue
You most likely don't need to loop here, Are you aware that you can do stuff like this?
你很可能不需要在这里循环，你知道你可以做这样的事情吗？
Range("A1:A10").Formula = "=SQRT(B1)"
Excel will increment the ref per row.
Excel 将增加每行的 ref。
回答by C Mackay
Extending this question: What I want to do is self explanatory:
扩展这个问题：我想做的是不言自明：
Dim max1, sig1 As Integer
max1 = Range("B5")
sig1 = Range("I5")
Range("B9").Select
ActiveCell.FormulaR1C1 = "=INDEX(LINEST((R1C[" & sig1 & "]:R1C[" & max1 & "]),),1)"
Fails on last line as it interprets this as: INDEX(LINEST((R4C:R4C),),1) Works fine if I tell it where these cells I want to fit as in:
在最后一行失败，因为它将此解释为： INDEX(LINEST((R4C:R4C),),1) 如果我告诉它我想要适合的这些单元格的位置，则工作正常：
ActiveCell.FormulaR1C1 = "=INDEX(LINEST((R1C2:R1C1793),),1)"
Have confirmed that max1 = 1793, sig1 = 1009, and Row 1 contains 4096 integers.
已确认max1 = 1793，sig1 = 1009，第1行包含4096个整数。
