VLOOKUP with two Workbooks

[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

 

[Dave Peterson]

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