vba 用于 vlookup 的 excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16804378/
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
excel macro for vlookup
提问by Benjamin Alonso Tan
I have 2 different workbooks with a set of parameters, e.g. car parts number, sales prices, etc. The 2 different workbooks will always have the same car parts numbers but they are not in order. So I was thinking of using a vlookupto match the parameters on one workbook to the other related to the respective parts' numbers.
我有 2 个不同的工作簿，其中包含一组参数，例如汽车零件编号、销售价格等。这 2 个不同的工作簿将始终具有相同的汽车零件编号，但它们并不按顺序排列。因此，我正在考虑使用 avlookup将一个工作簿上的参数与与相应零件编号相关的另一个参数匹配。
Thus, I used vlookupto perform this task. It works, but I want to implement this using a macro, so I would not need to manually do the vlookupevery time. Is it possible to create such a macro given that the workbooks (file names) would be different every time?
因此，我曾经vlookup执行过这个任务。它有效，但我想使用宏来实现它，所以我不需要vlookup每次都手动执行。鉴于工作簿（文件名）每次都不同，是否可以创建这样的宏？
I actually tried recording the macro and the vlookuprecords the parameters it needs relating to the file name.
我实际上尝试记录宏并vlookup记录它需要的与文件名相关的参数。
EDIT: code from comment:
编辑：来自评论的代码：
Sub Macro1()
ActiveCell.FormulaR1C1 = "=VLOOKUP('[TI_DBP_effective_06 May 2013.xls]NON SLL'!C1,'[TI_DBP_effective_06 May 2013.xls]NON SLL'!C1:C3,3,FALSE)"
Range("I1").Select Selection.AutoFill Destination:=Range("I1:I9779")
Range("I1:I9779").Select
End Sub
回答by David Zemens
Try something like this. You will have to place this macro in your Personal macro workbook, so that it is available all the time, no matter what workbooks are open. It will prompt you for two files, and then open them, and should insert the formula. Let me know if it gives you any trouble since I am not able to test it right now.
尝试这样的事情。您必须将此宏放在您的个人宏工作簿中，以便它始终可用，无论打开什么工作簿。它会提示你输入两个文件，然后打开它们，应该插入公式。如果它给您带来任何麻烦，请告诉我，因为我现在无法对其进行测试。
NOTE: This looks up the value one column to the LEFT of the cell you select, and then looks in columns 1:3 of the other file. Modify as needed.
注意：这会在您选择的单元格左侧的一列中查找值，然后在其他文件的 1:3 列中查找。根据需要进行修改。
Sub Macro1()
Dim file1 As String
Dim file2 As String
Dim wbSource As Workbook
Dim wbLookup As Workbook
Dim startRange As Range
file1 = Application.GetOpenFilename(Title:="Select the file to update")
If Len(Dir(file1)) = 0 Then Exit Sub
file2 = Application.GetOpenFilename(Title:="Select the LOOKUP file")
If Len(Dir(file2)) = 0 Then Exit Sub
Set wbLookup = Workbooks.Open(file2)
Set wbSource = Workbooks.Open(file1)
On Error Resume Next
Set startRange = Application.InputBox("Select the first cell for the formula", "Autofill VLOOKUP", Type:=8)
On Error GoTo 0
If Not startRange Is Nothing Then
Application.Goto startRange
startRange.FormulaR1C1 = "=VLOOKUP('[" & wbSource.Name & "]NON SLL'!RC[-1],'[" & wbLookup.Name & "]NON SLL'!C1:C3,3,FALSE)"
startRange.AutoFill Destination:=startRange.End(xlDown)
End If
End Sub
