VBA 将函数 VLOOKUP 插入范围，在其他工作簿中查找范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20155953/
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
VBA Insert function VLOOKUP into range,lookup range in other workbook
提问by user3022936
VBA Insert function VLOOKUP into range,lookup range in other workbook. The file containing the lookup table is achieved using filename_AcctMgr = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select"), then opening the file. Let's call this workbook2.
VBA 将函数 VLOOKUP 插入范围，在其他工作簿中查找范围。包含查找表的文件是使用 实现的filename_AcctMgr = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select")，然后打开文件。我们称此工作簿为 2。
In workbook1 I am adding the VLOOKUP formula into "F2" and looking up Column "A" values in workbook2, columns A:C. I Then copy the formula to all rows of column "F".
在工作簿 1 中，我将 VLOOKUP 公式添加到“F2”中，并在工作簿 2 的 A:C 列中查找列“A”值。I 然后将公式复制到“F”列的所有行。
I cannot find the syntax required to properly reference the workbook2 range in columns A:C.
我在 A:C 列中找不到正确引用 workbook2 范围所需的语法。
ActiveCell.Formula = _
"=VLOOKUP(activecell.offset(0,-5).address,'ws.name'!A:C,3,FALSE)"
Can anyone suggest the proper syntax?
谁能建议正确的语法？
回答by Reafidy
Try this:
尝试这个：
Range("F2").Resize(10).Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"
Or
或者
Range("F2:F10").Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"
EDIT:Sorry I forgot the piece about the filename as a variable:
编辑：对不起，我忘记了关于文件名作为变量的部分：
Dim MyFile As String
Dim vSplit As Variant
Dim iCnt As Integer
MyFile = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select")
vSplit = Split(MyFile, "\")
iCnt = UBound(vSplit)
vSplit(iCnt) = "[" & vSplit(iCnt) & "]"
MyFile = Join(vSplit, "\")
Range("F2:F10").Formula = "=VLOOKUP(A2,'" & MyFile & "Sheet1'!$A:$C,3,FALSE)"
You will need to add error handling in case someone clicks cancel. Also I doubt you want to add the formula to allrows in column f so just define the range you want. My examples is rows 2 to 10.
如果有人点击取消，您将需要添加错误处理。此外，我怀疑您是否要将公式添加到f 列中的所有行，因此只需定义所需的范围。我的例子是第 2 到 10 行。
回答by Floris
I am assuming you want the name of the sheet / range to be in a variable, rather than hard-coded. As it it, you have the name of the variable in the middle of your string, but it will be treated as a string, not a variable containing a string.
我假设您希望工作表/范围的名称在变量中，而不是硬编码。就像它一样，您在字符串的中间有变量的名称，但它将被视为字符串，而不是包含字符串的变量。
I suggest that you do something like the following:
我建议您执行以下操作：
Dim sheetName, lookupFrom, myRange ' always declare your variables
sheetName = "This is the sheet name" ' note I added some spaces to make it challenging
lookupFrom = ActiveCell.Offset(0, -5).address
myRange = "'" & sheetName & "'!A:C" ' putting quotes around the string so it's always valid
ActiveCell.Formula = "=VLOOKUP(" & lookupFrom & "," & myRange & ", 3, FALSE)"
You can of course do this all at once - it just gets messy to look at:
你当然可以一次完成这一切 - 看起来很混乱：
ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset(0, -5).Address & ", '" & sheetName & "'!A:C, 3, TRUE)"
Further note - the sheetNamecan of course contain the name of the other workbook - but you need name of workbook AND sheet... so
进一步注意 -sheetName当然可以包含另一个工作簿的名称 - 但你需要工作簿和工作表的名称......所以
sheetName = "[Book2]Sheet1"
would be fine.
会好的。
In your example you used ws.name(without proper quoting) - but that would not have given you the full path since you need both the workbook and the worksheet name to make sure you reference the right data. Better be explicit - if Excel can make the wrong assumptions about what you want, it will - and you will be left scratching your head...
在您使用的示例中ws.name（没有正确引用） - 但这不会为您提供完整路径，因为您需要工作簿和工作表名称以确保引用正确的数据。最好是明确的 - 如果 Excel 可以对你想要的东西做出错误的假设，它会 - 你会摸不着头脑......
Since you actually showed you had opened the book, you have the name of the file (the workbook) in your variable filename_AcctMgr. Then you should be able to use:
由于您实际上表明您已经打开了这本书，因此您的变量中有文件（工作簿）的名称filename_AcctMgr。那么你应该能够使用：
sheetName = "[" & filename_acctMgr & "]Sheet1"
and take it from there.
并从那里拿走。
