VLOOKUP with two Workbooks

  • Thread starter Thread starter Fikaman
  • Start date Start date
F

Fikaman

Hello,

I have two workbooks. On "list.xls" I have a spare parts list, and I want to
search for different items form another workbook (Search.xls) using VLOOKUP
in VBA.

Actually I have this code in "search.xls", but I get "Syntax error":


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Result As Variant

Result =
Application.vlookup(Range("A1"),'D:\[list.xls]Sheet1!$A$1:$D$1999,2,0)
If IsError(Result) Then
Result = 0
End If

cell(B1) = Result
End Sub




Any help is greatly appreciated.

Erik
 
For this kind of thing to work, List.xls will have to be open.

Another option is to plop the formula into an unused cell, then grab the value
from that cell, then clean up that cell.

Is there a reason why you just don't use a formula in the cell to start?
Hello,

I have two workbooks. On "list.xls" I have a spare parts list, and I want to
search for different items form another workbook (Search.xls) using VLOOKUP
in VBA.

Actually I have this code in "search.xls", but I get "Syntax error":

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Result As Variant

Result =
Application.vlookup(Range("A1"),'D:\[list.xls]Sheet1!$A$1:$D$1999,2,0)
If IsError(Result) Then
Result = 0
End If

cell(B1) = Result
End Sub

Any help is greatly appreciated.

Erik
Click to expand...
 
Back
Top