使用 A1 参考将 R1C1 中记录的 VLookUp 公式转换为 VBA 公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42247067/
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
Converting Recorded VLookUp formula in R1C1 into VBA formula using A1 reference
提问by Rishabh Jaiswal
I have this recorded macro that I want to convert to plain VBA macro code and copy the formula from AE2to lastrow.
我有这个录制的宏，我想将其转换为普通的 VBA 宏代码并将公式复制AE2到 lastrow。
"=VLOOKUP(RC[-22],'[test.xlsx]Sheet3'!C5:C6,2,0)" ' this is from another workbook
Trying to convert to VBA.
正在尝试转换为 VBA。
dim wbSLW as workbook
dim wbSLWDir as String
wbSLWDir = "C\Documents\test.xlsx" 'this is not the constant directory or file name
set wbSLW = workbooks.open(wbSLWDir)
ThisWorkbook.Activate
With Thisworkbook.Sheets(1)
.Range("AE2") = "=VLOOKUP(I2," & wbSLW & "!E:F,2,0)" ' error line
end with
When I've converted it, it returns Object does not support this property or method.
当我转换它时，它返回Object does not support this property or method.
采纳答案by R3uK
Cleaned recorded code :
清理记录的代码：
With ActiveSheet.Range("AE2")
.FormulaR1C1 = "=VLOOKUP(RC[-22],Temp!C[-30]:C[-29],2,0)"
.AutoFill Destination:=Range("AE2:AE182"), Type:=xlFillDefault
End With 'ActiveSheet
RC[-22]refers to a cell that is 22 columns before the cell in which you have the formula
here AE2is the initial cell, so RC[-22]=I2
RC[-22]指的是在其中具有下式的细胞前22列的细胞
在这里AE2是在初始小区，所以RC[-22]=I2
C[-30]and C[-29]refers respectively to columns that are 30 and 29 columns before the cell in which you have the formula
here AE2, so C[-30]=Aand C[-29]=B
C[-30]和C[-29]分别指的是在其中具有下式的细胞前30个29列的列
在这里AE2，因此C[-30]=A和C[-29]=B
Changed formula :
改变公式：
Sheets(1).Range(perNum & 2).Formula = "=VLOOKUP(I2,Temp!A:B,2,0)"
or without converting the formula :
或不转换公式：
Sheets(1).Range(perNum & 2).FormulaR1C1 = "=VLOOKUP(RC[-22],Temp!C[-30]:C[-29],2,0)"
回答by Teoanadech
Change your settings so that it does not use R1C1 format by going to (Excel 2010) Settings / Formulas and then unchecking the R1C1 reference style. Rerecord your macro and then edit it. You should not have any "RC" references, but if you do then change all references that have "RC" in them to the the same style as in "I2".
通过转到（Excel 2010）设置/公式，然后取消选中 R1C1 参考样式，更改您的设置，使其不使用 R1C1 格式。重新录制您的宏，然后对其进行编辑。你不应该有任何“RC”引用，但如果你这样做了，那么将所有包含“RC”的引用更改为与“I2”中相同的样式。
回答by Rishabh Jaiswal
If i understood correctly, you want to replace name of the workbook in below line in vbacode :
如果我理解正确，您想在vba代码中替换以下行中的工作簿名称：
=VLOOKUP(RC[-22],'[**test.xlsx**]Sheet3'!C5:C6,2,0)
Simplest way to do that without changing any other code is :
在不更改任何其他代码的情况下执行此操作的最简单方法是：
=VLOOKUP(RC[-22],'[**" & wbSLW.Name & "**]Sheet3'!C5:C6,2,0)
回答by Diego Ribba
Assuming that Active Sheet name is Sheet1
假设活动工作表名称是 Sheet1
ActiveCell.Value = Application.WorksheetFunction.VLookup(Sheets("Sheet1").Range("I2"), Sheets("Temp").Range("A:B"), 2, 0)
