在 VBA 中使用 VLookup 来引用另一个 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32744128/
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 VBA to reference another Excel file
提问by zaanwar
I'm trying to program a VLookup Table in VBA that references another file. Here is a simple outline of my goal:
我正在尝试在 VBA 中编写一个引用另一个文件的 VLookup 表。这是我的目标的简单概述：
- Look up value in cell A2 in another Excel file
- Pull the information in from column 2 of the other Excel file and place in Cell B2
- Move on to cell A3 and repeat the process until there are no more entries left in column A
- 在另一个 Excel 文件中查找单元格 A2 中的值
- 从另一个 Excel 文件的第 2 列中提取信息并放入单元格 B2
- 移动到单元格 A3 并重复该过程，直到 A 列中没有更多条目为止
Here is the code that I already have. I keep getting an error that says "Unable to get the VLookup property of the WOrksheetFunction class." I checked the other posts referencing that error but they were not of any help. Do you all see an error in my code? Or does anyone have a better way of accomplishing this task?
这是我已经拥有的代码。我不断收到一条错误消息，指出“无法获取 WOrksheetFunction 类的 VLookup 属性”。我检查了引用该错误的其他帖子，但它们没有任何帮助。你们都看到我的代码中有错误吗？或者有没有人有更好的方法来完成这项任务？
Sub SBEPlannerAdder()
Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")
With Sheets("Sheet1")
' Selects the first cell to check
Range("A2").Select
Dim x As Variant
x = wbk.Worksheets("Sheet1").Range("A1:C1752")
' Loops through all rows until an empty row is found
Do Until IsEmpty(ActiveCell)
Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup((ActiveCell.Column & ActiveCell.Row), x, 2, 0)
ActiveCell.Offset(1, 0).Select
Loop
End With
Call wbk.Close(False)
End Sub
回答by
When you open a workbook, it becomes the active workbook. It seems you were never passing control back to the target workbook.
当您打开工作簿时，它将成为活动工作簿。似乎您从未将控制权传递回目标工作簿。
Sub SBEPlannerAdder()
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")
Set x = extwbk.Worksheets("Sheet1").Range("A1:C1752")
With twb.Sheets("Sheet1")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub
See How to avoid using Select in Excel VBA macrosfor more methods on getting away from relying on select and activate to accomplish your goals.
有关摆脱依赖选择和激活来实现目标的更多方法，请参阅如何避免在 Excel VBA 宏中使用选择。
回答by tells
It depends on whether you plan to do this as a one off or repeatedly. I'm assuming repeatedly since doing this manually is not all that difficult.
这取决于您是否计划一次性或重复执行此操作。我反复假设，因为手动执行此操作并不是那么困难。
The first thing I would look at is your arguments. The first two should be ranges. So to be clear, perhaps you could do something like
我首先要看的是你的论点。前两个应该是范围。所以要清楚，也许你可以做类似的事情
Dim x As Range
set x = wbk.Worksheets("Sheet1").Range("A1:C1752")
...
Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup(Range(Activecell.Address), x, 2, 0)
The important bits are making sure your first two arguments are Ranges for the Vlookup function.
重要的一点是确保你的前两个参数是 Vlookup 函数的范围。
