vba 在宏函数中使用 Vlookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17883693/
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 in a macro function
提问by Jake Ison
I need to use Vlookup macro =VLOOKUP(E2,[gpic.xlsx]Sheet1!$A:$D,4,FALSE) for every cell until it ends. I'm not sure how to use long instead of range. When I use range it sometimes goes over because I predict wrong and I want everything to be automated, can anyone help please? instead of the E2 I need it to go through all of them but I want to incorporate it into my macro.
我需要对每个单元格使用 Vlookup 宏 =VLOOKUP(E2,[gpic.xlsx]Sheet1!$A:$D,4,FALSE) 直到它结束。我不确定如何使用 long 而不是 range。当我使用 range 时，它有时会因为我预测错误而结束，我希望一切都自动化，有人可以帮忙吗？而不是 E2 我需要它来完成所有这些，但我想将它合并到我的宏中。
Sub Work()
Dim LR As Long
Dim row As String
row = "E2"
row = "E" & x
LR = Range("E" & Rows.Count).End(xlUp).row
Range(Columns(6), Columns(7)).Select
For x = 0 To 2
row = "E" & x
Range("F2:F" & LR).Formula = "=VLOOKUP(" & row & ",[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
Next
End Sub
回答by cle_joe
To expand on what JDuarteDJ said, using a variable to loop through the rows would likely work best. However, you mentioned sometimes you predict the number of rows incorrectly. You could use:
为了扩展 JDuarteDJ 所说的内容，使用变量循环遍历行可能效果最好。但是，您提到有时您会错误地预测行数。你可以使用：
rowCount = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row
rowCount = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row
This will give you the count of the rows with somthing in them in column F. Then you can do the same loop that JDuarteDJ mentioned only instead of
这将为您提供 F 列中包含某些内容的行数。然后您可以执行 JDuarteDJ 提到的相同循环，而不是
x = 2 to 20
x = 2 到 20
You could use
你可以用
For x = 2 to rowCount
' Do all things previously mentioned and whatver you need to do
Next
For x = 2 to rowCount
' 做前面提到的所有事情以及你需要做的任何事情
Next
Hope this helps
希望这可以帮助
-------------UPDATE---------------------------
The problem with the edit, I THINK, is that within your for loop, you're using:
-------------更新---------------------------
编辑的问题，我认为，在您的 for 循环中，您使用的是：
Range("F2:F" & LR).FormulaR1C1 = "=VLOOKUP(&row&,[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
Range("F2:F" & LR).FormulaR1C1 = "=VLOOKUP(&row&,[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
Which is isn't iterating through the correct number of times. What you want to be doing in my opinion is looping from 2 to the number of rows, like this:
这不是迭代正确的次数。在我看来，您想要做的是从 2 循环到行数，如下所示：
For x = 2 to LR
Range("F2:F" & X).FormulaR1C1 = "=VLOOKUP(&row&,[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
对于 x = 2 到 LR
Range("F2:F" & X).FormulaR1C1 = "=VLOOKUP(&row&,[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
回答by JDuarteDJ
Replace E2 for a variable:
将 E2 替换为变量：
Dim row as String
row = "E2"
Dim row as String
row = "E2"
then use a loop to iterate through all E2,E3,E4 etc.
然后使用循环遍历所有 E2、E3、E4 等。
For x=2 to 20 do
row = "E" & x
formula = "VLOOKUP("&row&",[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
... use your code here ...
对于 x=2 到 20 做
row = "E" & x
formula = "VLOOKUP("&row&",[gpic.xlsx]Sheet1!$A:$D,4,FALSE)"
...在这里使用你的代码.. .
My VB may be a little rusty :/
Hope this helps
我的 VB 可能有点生疏：/
希望这会有所帮助
