使用 VLOOKUP 引用 VBA excel 2007 中的另一张工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20241923/
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 VLOOKUP referencing another sheet in VBA excel 2007
提问by Chris
I know many questions have been both asked and answered around this subject, but I couldn't find anything that worked for me as I'm referencing another sheet (Lookuptable).
我知道围绕这个主题已经提出和回答了很多问题，但是当我引用另一张表（Lookuptable）时，我找不到任何对我有用的东西。
Below is the original VLOOKUP as written in excel, this works fine:
下面是用 excel 编写的原始 VLOOKUP，这很好用：
=VLOOKUP(A1,Lookuptable!A:B, 2, FALSE)
Below is a stripped back pseudo codeversion of my script, with the VBA vlookup line of code that is providing the issue. I'm basically looking for the VBA version of the above that works!
下面是我的脚本的剥离伪代码版本，其中包含提供问题的 VBA vlookup 代码行。我基本上是在寻找上述有效的 VBA 版本！
Sub Test()
Dim rng As Range
Dim result As Variant
Dim i As Long
Dim rng2 As Range
Dim arg4 As Boolean
Dim arg1 As Long, arg3 As Long
With ActiveSheet
Set rng = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
rng.Cells(i, 2) = Application.WorksheetFunction.VLookup(i, Sheets(Lookuptable).Range("A1:B1"), 2, False)
End Sub
It's probably worth noting that I ether get a subscript out of range error or a invalid procedure call or argument error.
可能值得注意的是，我收到了下标超出范围错误或无效的过程调用或参数错误。
回答by Michael
Try this:
尝试这个：
Sub Test()
Dim rng As Range
Dim i As Long
With ActiveSheet
Set rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
For i = 2 to rng.Rows.Count
rng.Cells(i, 2) = Application.WorksheetFunction.VLookup(.Cells(i,1), Sheets("Lookuptable").Range("A:B"), 2, False)
Next
End With
End Sub
