vba - 跨工作簿Vlookup

Question

嘿，大家好，我正在尝试通过跨工作簿执行 vlookup。我试图这样写..但使用“x”和“x2”似乎不起作用..

Folder = ActiveWorkbook.Path + "\"

Dim OptioneeManWb As Workbook
Dim TransOutWb As Workbook
Dim TransOutWs As Worksheet
Dim TermWb As Workbook
Dim TermWs As Worksheet

Set OptioneeManWb = Workbooks("optionee statement manual.xlsx")
Set TransOutWb = Workbooks.Open(Folder & "employee transfer out.xlsx")
Set x = TransOutWb.Worksheets("out").Range("A:C")
Set TermWb = Workbooks.Open(Folder & "employee terminated listing.xlsx")
Set x2 = TermWb.Worksheets("terminated").Range("A:C")


OptioneeManWb.Sheets("manual optionee stmt").Range("C6:C" & lastrow2).Formula = "=VLOOKUP(B:B,x,3,0)"
OptioneeManWb.Sheets("manual optionee stmt").Range("D6:D" & lastrow2).Formula = "=VLOOKUP(B:B,x2,3,0)"
OptioneeManWb.Sheets("manual optionee stmt").Range("C6:C" & lastrow2, "D6:D" & lastrow2).NumberFormat = "m/d/yyyy"
OptioneeManWb.Sheets("manual optionee stmt").Range("C:F").Copy
OptioneeManWb.Sheets("manual optionee stmt").Range("C:F").PasteSpecial xlPasteValues

TransOutWb.Close
TermWb.Close

Answer 1

VLOOKUP 等待范围地址作为第二个参数。

.Formula = "=VLOOKUP(B:B," & x.Address(External:=True) & ",3,0)"

在您的情况下 "=VLOOKUP(B:B,x,3,0)" x 未被识别为变量，因为它位于字符串内。此外，您还需要在此处填写地址(以外部格式填写，以便不同的工作簿也能被识别)。另请参阅Range.Address Property (Excel)获取信息。

还要声明变量以确保其类型为范围:在过程顶部的 Dim x As Range, x2 As Range。

<小时/>

OptioneeManWb.Sheets("manual optionee stmt").Range("C6:C" & lastrow2).Formula = "=VLOOKUP(B:B," & x.Address(External:=True) & ",3,0)"
OptioneeManWb.Sheets("manual optionee stmt").Range("D6:D" & lastrow2).Formula = "=VLOOKUP(B:B," & x2.Address(External:=True) & ",3,0)"