VBA VLookup Formula Error: "Subscript Out Of Range"

[Mav3000]

Hi,

I have the following Vlookup formula in a cell, which works fine:

=VLOOKUP(J11,'BT Site Directory.xls'!Directory, 2, FALSE)

I need this in a VBA Form, however when I convert it to what I think will work, I get the error "Subscript Out Of Range" on the Vlookup line.

The code is:

Dim Skyline As String
Dim ExchangeName As String

Dim LookupWorkbook As String
Dim LookupRange As String

LookupWorkbook = "BT Site Directory.xls"
LookupRange = "Directory"

Skyline = FrmLookup.TbxSkyline.Value

ExchangeName = Application.VLookup(Skyline, Workbooks(LookupWorkbook).Range(LookupRange), 1, False)

FrmLookup.TbxExchangeName.Value = ExchangeName

I've tried it without passing in variables but have seperated them to check that they are all populating correctly.

Where am I going wrong?!!

Any help would be greatly appreciated.

Regards,

Richard

 

[Mav3000]

I've also tried this line as a replacement (no variables and the inclusion of the worksheet property):

ExchangeName = Application.VLookup(FrmLookup.TbxSkyline.Value, Workbooks("BT Site Directory.xls").Worksheets("TE Directory").Range("Directory"), 1, False)

Which produces the same error. I can't see why the Vlookup in the cell works, but the VBA equivalent does not.

 

[xlbo]

and this:

FrmLookup.TbxSkyline.TEXT

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chance1234]

Take a look at Referring to Named Ranges in the VBA help

your problem is with this bit

Workbooks(LookupWorkbook).Range(LookupRange)

Your string for the Vlookup in your cae hsould look something like

BT Site Directory.xls!Directory




Chance,

Filmmaker, gentleman and ROMAN!

 

[Chance1234]

ignore my last post, not enough coffee this morning , will post back very shortly






Chance,

Filmmaker, gentleman and ROMAN!

 

[Chance1234]

I created a quick list of two columns, one contain numebrs the other letters, so in my code below I have used an integer where you will need to use a string

    Dim wrkbk As Workbook
    Dim rngLook As Range
    Dim strWrk As String
    Dim strRange As String
    Dim intSkyline As Integer
    Dim ExchangeName As String
    
    intSkyline = 1
    
    strWrk = "Book1.xls"
    strRange = "Direct"
    
    Set wrkbk = Workbooks(strWrk)
    Set rngLook = wrkbk.Names(strRange).RefersToRange
    
    ExchangeName = Application.VLookup(intSkyline, rngLook, 2)
    MsgBox ExchangeName

Chance,

Filmmaker, gentleman and ROMAN!

 

[Mav3000]

Hi Chance,

I'm still getting the subscript out of range error.

I'm using Excel 2002 by the way.

This is my function:

Dim wrkbk As Workbook
Dim rngLook As Range
Dim strWrk As String
Dim strRange As String
Dim Skyline As String
Dim ExchangeName As String

Skyline = FrmLookup.TbxSkyline.Value

strWrk = "BT Site Directory.xls"
strRange = "Directory"

Set wrkbk = Workbooks(strWrk) 'Error Appears Here***
Set rngLook = wrkbk.Names(strRange).RefersToRange

ExchangeName = Application.VLookup(intSkyline, rngLook, 2)
MsgBox ExchangeName

---------------------------------

I've triple-checked the file name is correct. The file is sat next to the workbook I'm working in in the same directory.

 

[Chance1234]

firslt try strwrk ="[BT Site Directory.xls]"

btw, Is BT Site Directory.xls open at the time when this code is running ?


Chance,

Filmmaker, gentleman and ROMAN!

 

[Mav3000]

Hi Chance,

I've checked taskmanager that there are no other instances of Excel open, and the workbook is closed.

I've now created a new workbook (Book1.xls), with a range called "Directory". Column one is numbered 1-12, column 2 lists Jan-Dec.

My code now looks like this:

Dim wrkbk As Workbook
Dim rngLook As Range
Dim strWrk As String
Dim strRange As String
Dim Skyline As String
Dim ExchangeName As String

Skyline = "2"

strWrk = "Book1.xls"

strRange = "Directory"

Set wrkbk = Workbooks(strWrk) 'ERROR HERE ***
Set rngLook = wrkbk.Names(strRange).RefersToRange

ExchangeName = Application.VLookup(Skyline, rngLook, 2)
MsgBox ExchangeName

-----------------
Again, Subscript out of range on the Set wrkbk = line!

 

[Chance1234]

Ah the workbook needs to be open for this approach to work.

the reason you are getting that subscript error as excel has no idea what workbook you are talking about, you need to open the spreadsheet up in your code and then reference the range.

There is another method which involves ADO

http://www.vbaexpress.com/forum/archive/index.php/t-260.html

Chance,

Filmmaker, gentleman and ROMAN!

 

[Mav3000]

Of course!

Thanks very much in helping me find this answer - I can't believe now I look at it that I need to open the spreadsheet first and didn't realise!

I'll turn screenupdating off and use ADO.

Cheers,

Richard