VBA Excel R1C1Formulas 使用单元格方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8781338/
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
VBA Excel R1C1Formulas using cells method
提问by Bippy
I've run accross this problem many times and still haven't found the solution or why this won't work. I want to use cells method to enter a formula through a column and so I write this:(just an example)
我已经多次遇到这个问题，但仍然没有找到解决方案或为什么这不起作用。我想使用单元格方法通过列输入公式，所以我写了这个：（只是一个例子）
With ws
iEndCol = .cells(4650,1).End(Xlup).Column
For i = 2 To iEndCol
.Cells(i, 2) = "=VLOOKUP([RC-1],Somesheet!someTable,10,FALSE)"
Next
End With
when this dosen't work (Method error) I try something like this:
当这不起作用（方法错误）我尝试这样的事情：
Cells(i,2).Select
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
ActiveCell.Formula = "=VLOOKUP([RC-1],Somesheet!someTable,10,FALSE))"
ActiveCell.Offset(1, 0).Select
Loop
or instead of .Formula, I try .FormulaR1C1, .Formulalocal etc. and this doesn't work either. Then this is what works:
或代替 .Formula，我尝试 .FormulaR1C1、.Formulalocal 等，但这也不起作用。然后这是有效的：
Range("B2").Select
Do Until IsEmpty(ActiveCell.Offset(0, 5)) And IsEmpty(ActiveCell.Offset(0, 6))
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Formula = "=VLOOKUP(B2,Somesheet!someTable,10,FALSE)"
End If
ActiveCell.Offset(1, 0).Select
Loop
What am I not understanding on using Cells to enter formulas?
我对使用单元格输入公式有什么不理解？
采纳答案by GSerg
Enter a formula using Excel interface (not your code).
使用 Excel 界面（不是您的代码）输入公式。
Now go to the code editor, press Ctrl+G and type: ? activecell.FormulaR1C1
现在转到代码编辑器，按 Ctrl+G 并键入： ? activecell.FormulaR1C1
The result, =VLOOKUP(RC[-1],Somesheet!sometable,10,FALSE), will tell you what you are doing wrong. You are not providing correct RC syntax.
结果，=VLOOKUP(RC[-1],Somesheet!sometable,10,FALSE)，会告诉你你做错了什么。您没有提供正确的 RC 语法。
Having that said, you should always ensure your formula syntax matches the property you have picked to set that formula. Use A1 notation for .Formula, and RC notation for FormulaR1C1. And don't use .Valueto set a formula.
话虽如此，您应该始终确保您的公式语法与您为设置该公式而选择的属性相匹配。对 使用 A1 表示法，对 使用.FormulaRC 表示法FormulaR1C1。并且不要.Value用于设置公式。
回答by Thomas
First, the following worked for me:
首先，以下对我有用：
Set oCell = ActiveCell
Do
Set oCell = oCell.Offset(0, 1)
oCell.FormulaR1C1 = "=VLOOKUP(RC[-1],SomeTable,10,FALSE)"
Set oCell = oCell.Offset(1, -1)
Loop Until IsEmpty(oCell)
Notice that in my syntax, I assumed that SomeTablewas a defined name with Workbook scope and thus I need no prefix. If SomeTableis a defined name scoped to a specific Worksheet, only then do you need to prefix the sheet name (e.g. Somesheet!SomeTable).
请注意，在我的语法中，我假设这SomeTable是一个具有 Workbook 范围的定义名称，因此我不需要前缀。如果SomeTable是一个限定于特定工作表的定义名称，那么您才需要在工作表名称前加上前缀（例如Somesheet!SomeTable）。
Second, you should verify in which cell it is trying to put the formula using Debug.Print oCell.Address. It may be the case that it is trying to stuff the formula in literally the first column which would cause an error in the formula.
其次，您应该使用Debug.Print oCell.Address. 可能的情况是它试图将公式填入第一列，这会导致公式出错。
