VBA将函数VLOOKUP插入范围，在其他工作簿中查找范围

Question

VBA将函数VLOOKUP插入范围，在其他工作簿中查找范围。包含查找表的文件是使用filename_AcctMgr = Application.GetOpenFilename(, , "Select Acct Mgr File", "Select")实现的，然后打开该文件。让我们称之为工作簿2。

在工作簿1中，我将VLOOKUP公式添加到"F2"中，并在工作簿2中的A:C列中查找"A"列的值。I然后将公式复制到列"F"的所有行。

在A:C列中找不到正确引用工作簿2范围所需的语法。

ActiveCell.Formula = _
    "=VLOOKUP(activecell.offset(0,-5).address,'ws.name'!A:C,3,FALSE)"

有人能建议正确的语法吗？

Answer 1

试试这个：

Range("F2").Resize(10).Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"

或

Range("F2:F10").Formula = "=VLOOKUP(A2,[Book2]Sheet1!$A:$C,3,FALSE)"

编辑：对不起，我忘记了关于文件名作为变量的文章：

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)"    

您需要添加错误处理，以防有人单击"取消"。此外，我怀疑您是否希望将公式添加到f列的所有行中，因此只需定义所需的范围即可。我的例子是第2行到第10行。

Answer 2

我假设您希望工作表/范围的名称在变量中，而不是硬编码。实际上，变量的名称位于字符串的中间，但它将被视为字符串，而不是包含字符串的变量。

我建议你做以下事情：

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)"

你当然可以一次完成这一切——看起来会很混乱：

ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset(0, -5).Address & ", '" & sheetName & "'!A:C, 3, TRUE)"

请注意，sheetName当然可以包含其他工作簿的名称，但您需要工作簿的名称和工作表。。。所以

sheetName = "[Book2]Sheet1"

那就好了。

在您的示例中，您使用了ws.name（没有正确的引号），但这不会给您完整的路径，因为您需要工作簿和工作表名称来确保引用正确的数据。最好明确一点——如果Excel能对你想要的东西做出错误的假设，它会的——你会挠头。。。

由于您实际显示您已经打开了这本书，因此您在变量filename_AcctMgr中有了文件（工作簿）的名称。然后你应该能够使用：

sheetName = "[" & filename_acctMgr & "]Sheet1"

然后从那里拿走。