如何在 VBA 中的 VLOOKUP 中放置范围变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22168546/
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
How to put a range variable inside VLOOKUP in VBA?
提问by Ale
I'm doing a VLOOKUP function between two columns, using folloving macro:
我正在使用以下宏在两列之间执行 VLOOKUP 函数：
Sub ExcelJoin()
On Error Resume Next
Dim Dept_Row1 As Long
Dim Dept_Clm1 As Long
Dim LastRowA As Long
Dim LastColO As Long
Set currentsheet = ActiveWorkbook.Sheets(1)
ctr = 0
LastRowA = currentsheet.Range("A" & Rows.Count).End(xlUp).Row
LastRowO = currentsheet.Range("O" & Rows.Count).End(xlUp).Row
Table1 = currentsheet.Range("A2:A" & LastRowA)
Table2 = currentsheet.Range("O2:O" & LastRowO)
Dept_Row1 = currentsheet.Range("B2").Row
Dept_Clm1 = currentsheet.Range("B2").Column
For Each cl In Table1
currentsheet.Cells(Dept_Row1, Dept_Clm1).FormulaR1C1 = "=VLOOKUP(RC[-1], R2C15:R14C15, 1, False)"
Dept_Row1 = Dept_Row1 + 1
ctr = ctr + 1
Next cl
End Sub
But as far the number of rows in a range R2C15:R14C15 is unknown, i should put R14 as a variable, using LastRowO. But i have some problems with synthases, cause i don't know how really put this variable into VLOOKUP in a correct way.
但至于 R2C15:R14C15 范围内的行数未知，我应该将 R14 作为变量，使用 LastRowO。但是我在合成酶方面有一些问题，因为我不知道如何真正以正确的方式将此变量放入 VLOOKUP 中。
回答by Dmitry Pavliv
This one should work:
这个应该有效：
currentsheet.Cells(Dept_Row1, Dept_Clm1).FormulaR1C1 = "=VLOOKUP(RC[-1], R2C15:R" & LastRowO & "C15, 1, False)"
btw, there is no need to use loop For Each cl In Table1. You could apply formula to entire range in one line of code.
顺便说一句，没有必要使用 loop For Each cl In Table1。您可以在一行代码中将公式应用于整个范围。
Change:
改变：
For Each cl In Table1
currentsheet.Cells(Dept_Row1, Dept_Clm1).FormulaR1C1 = "=VLOOKUP(RC[-1], R2C15:R14C15, 1, False)"
Dept_Row1 = Dept_Row1 + 1
ctr = ctr + 1
Next cl
to
到
currentsheet.Range("B2:A" & LastRowA).FormulaR1C1 = "=VLOOKUP(RC[-1], R2C15:R" & LastRowO & "C15, 1, False)"
