vba Vlookup跨工作簿 [英] vba Vlookup across workbooks

问题描述

我在下面的语法行中似乎有错误。我认为问题在于工作簿1的范围参数。我不知道为什么。基本上我正在追踪vlookup跨2个工作簿。

I seem to have an error in the below syntax line. I believe the issue lies with range parameter of workbook book1. I cannot figure out why. Basically I'm tring to vlookup across 2 workbooks.

代码从工作簿 - book1调用。就在这行代码工作簿 - book2被激活之前。这两本书都是开放的。我通过将左侧替换为变量变量来捕获错误代码2015。

The code is invoked from workbook - book1. Just before this line of code workbook - book2 is activated. Both the workbooks are open. I captured the error code 2015 by replacing the left side with a variant variable.

感谢对此vlookup问题的任何帮助。谢谢。

I appreciate any help with this vlookup issue. Thanks.

 Cells(j, c + 2).value = [VLookup(workbooks(book2).sheets(5).range(Cells(j, c + 1)), workbooks(book1).sheets(4).range(cells(row1+2,1),cells(row2,col1)), 3, false)]

推荐答案

您只提供了一段代码，但首先让我们确保您已定义所有变量。我还添加了一些简化，并可能帮助陷阱错误。

You've provided only a snippet of code, but first things first let's make sure you have all the variables defined. I have also added a few more to simplify and possibly help trap errors.

Sub VlookMultipleWorkbooks()
Dim lookFor as String
Dim srchRange as Range
Dim book1 as Workbook
Dim book2 as Workbook

'Set some Workbook variables:
Set book1 = Workbooks("Book 1 Name") '<edit as needed
Set book2 = Workbooks("Book 2 Name") '<edit as needed

'Set a string variable that we will search for:
lookFor = book2.sheets(5).range(Cells(j, c + 1))

'Define the range to be searched in Book1.Sheets(4):
Set srchRange = book1.Sheets(4).Range(cells(row1+2,1).Address, cells(row2,col1).Address)

'This assumes that the Book2 is Open and you are on the desired active worksheet:
ActiveSheet.Cells(j, c + 2).value = _
         Application.WorksheetFunction.VLookup(lookFor, _
         book1.Sheets(4).Range(srchRange.Address), 3, False)

End Sub

这篇关于vba Vlookup跨工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！