Syntax for VBA Vlookup to another workbook

Question

I'm trying to perform a VLOOKUP in VBA, using a table in a different workbook.

I've tried:

width = Application.VLookup(code, Workbooks("T:\Data\Dimensions.xlsx").Sheets("Main").Range("A61:G1500"), 7, False)

where code is a variable I've already set, but it just returns "Subscript out of range".

I'm sure you can see what I'm trying to do, but I'm guessing I've got the syntax wrong in the vlookup.

Thanks.

Answer 1

Make sure the target workbook is opened. Try this:

Set src = Workbooks.Open("T:\Data\Dimensions.xlsx")
width = Application.VLookup(code, src.Sheets("Main").Range("A61:G1500"), 7, False)

Answer 2

The code below is a little long, but it will work you through step-by-step, defining and setting all your objects (see comments in the code itself).

You also need to handle a scenario where Vlookup will not be able to find code in your specified Range,

Code

Option Explicit

Sub VlookupClosedWorkbook()

Dim Width, code 
Dim WorkbookName As String
Dim WB As Workbook
Dim Sht As Worksheet
Dim Rng As Range

WorkbookName = "Dimensions.xlsx"

' set the workbook object
On Error Resume Next
Set WB = Workbooks(WorkbookName) ' first try to see if the workbook already open
On Error GoTo 0
If WB Is Nothing Then ' if workbook = Nothing (workbook is closed)
    Set WB = Workbooks.Open("T:\Data\" & WorkbookName)
End If

' set the worksheet object
Set Sht = WB.Sheets("Main")

' set the Range object
Set Rng = Sht.Range("A61:G1500")

' verify that Vlookup found code in the Range
If Not IsError(Application.VLookup(code, Rng, 7, False)) Then
    Width = Application.VLookup(code, Rng, 7, False)
Else
    MsgBox "Vlookyp Error finding " & code
End If

End Sub

Answer 3

You have to open that workbook or use cell to get value from closed one

range("A1").formula = "=vlookup(" & code & ",'T:\Data\[Dimensions.xlsx]Main'!A61:G1500,7,0)"
range("A1").calculate
width = range("A1").value