Word/Excel Vba vlookup "Subscript out of range" error

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.

Answers


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.


Need Your Help

WordPress add_action, wp_footer and passing variables

wordpress function action arguments

Scenario: need to add a function to wp_footer, need it to be an action so I can add_ / do_ / remove_ it, and it needs to receive variables.

Timeout while trying to contact RemoteTestNG in Selenium on Mac

selenium testng osx-mountain-lion ubuntu-12.04

I have a TestNG setup running on Eclipse Indigo 20120216-1857 and TestNG for Eclipse v2.0. Dev environment is Mac OSX 10.8.2. Target is a web server running on Ubuntu 12.04.