Word/Excel Vba vlookup “Subscript out of range” error

Question

I'm trying to use vlookup to read a string from a word document and return corresponding values from an excel sheet. I seem to be getting a "Subscript out or Range" Error on the vlookup function.

This is the worksheet that i am looking at: Sample Data

The code I have is:

Sub Autofill()
Dim oExcel As New Excel.Application
Dim testdb As Excel.Workbook
Dim testvar1 As Double

Set testdb = oExcel.Workbooks.Open("k:\SIF\Vibration\Dbase.xlsm")

testvar1 = oExcel.WorksheetFunction.VLookup("Roger", testdb.Sheets("Main").Range("A1:C4"), 2, False)

MsgBox (testvar1)

End Sub

I tried using the Application.Vlookup function as well, to no avail. Got the same error.

This is a shot in the dark, but have you tried declaring oExcel as Excel.Application? Add this before setting testdb, set oExcel = new Excel.Application — Alex
You'd likely get this error if the name of the sheet Main doesn't exist in the Dbase.xlsm file. Maybe there's an extra space or something, check for accuracy. I emulated your code and it appears ok. One thing that might be an issue is if you are expecting to return a string, you may want to change testvar1 to be a String and not Double. — Ryan Wildry
I suspect you are getting your error because your Vlookup returns an error (since not found), you need to add error handling in cases your Vlookup is unsuccessful, see link : stackoverflow.com/questions/18063214/… — Shai Rado
i think you need to activate the excel window after open() and before .vlookup. based on my experience — Rosetta

Kevin Kevin · Accepted Answer · 2016-08-31T14:29:40

This script worked for me. Make sure the Main tab exists, that's how I duplicated the error.

A missing value in the VLookup will give you "Unable to get Vlookup property..." error.

Word/Excel Vba vlookup “Subscript out of range” error

1 Answers