VBA Vloopup使用2种不同的工作簿 [英] VBA Vloopup using 2 different workbooks

问题描述

我必须通过使用Vlookup从1 Workbook到另一个Workbook获取一些信息。原因是我有75个细胞需要从另一个工作簿填充价格，但在其他工作簿中不会总是有75个价格。

I have to get some information from 1 Workbook to another by using a Vlookup. The reason is that I got 75 cells that need to be filled with prices from another workbook but there will not always be 75 prices in the other workbook.

Sub Update()

Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = ActiveWorkbook

' Open Workbook "Verzamelstaat" '
rowl = ActiveCell.Row
linkl = Cells(rowl, 16).Value
Application.Workbooks.Open (linkl)
Filenamel = Mid(linkl, InStrRev(linkl, "\") + 1, Len(linkl))
Set wb2 = Workbooks(Filenamel)

' Copy data to other workbook '

wb1.Sheets(1).Range("AY4").Value = Application.VLookup(wb1.Sheets(1).Range("AY2").Value, wb2.sheets(1).Range("A:A"), 2, False)

' Close Workbook "Verzamelstaat" '
Workbooks(Filenamel).Close savechanges:=False

End Sub

我的问题是 wb1.Sheets（1）.Range（AY4）最终为#REF！。

The problem I have is that wb1.Sheets(1).Range("AY4") ends up as #REF!.

AY2 = 001的值而另一个工作簿在A列中有001到075。

The value of AY2 = 001 and the other workbook has 001 to 075 in column A.

我认为答案很简单，但我只是看不到它....

I think the answer is simple but I just don't see it....

推荐答案

更改您的 VLookUp函数的第二个参数，它指的是A和B列：

Change the second parameter of your VLookUp function that it refers to both A and B columns:

... = Application.VLookup(wb1.Sheets(1).Range("AY2").Value, wb2.sheets(1).Range("A:B"), 2, False)

这篇关于VBA Vloopup使用2种不同的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！