VLOOKUP in VBA

[ChillyWilly]

"ZipCodes" defined as name link to other workbook
In a worksheet : '=VLOOKUP("ABX",ZipCodes,2)' works fine!

in VBA : 'x = Application.WorksheetFunction.Vlookup("ABX","ZipCodes",2)' ->
error

If the range "ZipCodes" is in the same workbook : both work!

Who has the answer?

 

[JE McGimpsey]

"ZipCodes" defined as name link to other workbook
In a worksheet : '=VLOOKUP("ABX",ZipCodes,2)' works fine!

in VBA : 'x = Application.WorksheetFunction.Vlookup("ABX","ZipCodes",2)' ->
error

If the range "ZipCodes" is in the same workbook : both work!

Who has the answer?

I suspect you're not using MacXL, or at least not XL04, since
Application.WorksheetFunction.VLookup() produces an error, so one has to
use Application.VLookup() instead.

You don't say what error you're getting in your VBA example, so it's
hard to say what's happening.

Nonetheless, when passing arguments in VBA you should be more explicit
about what type of argument you're passing. The second argument in
VLookup expects a range reference.

This works for me (MacXL04) if (and only if) the workbook with the
defined name is active:

x = Application.VLookup("ABX", Range("ZipCodes"), 2)

This is a bit more robust, and works no matter what workbook is active:

x = Application.VLookup("ABX", _
ThisWorkbook.Names("ZipCodes").RefersToRange, 2)

 

[ChillyWilly]

tnx, for your quick responce, i'm using XP with Excel 2003
I've tried your suggestions, but they give the same error "1004 : Methode
range of object_global failed'
Notice that using a local named range has no problems, only when named range
refers to another Workbook, and the VLOOKUP function has no problems with the
named range when used directly in a worksheet in either case. Also when I
don't use the named range but refer to the range directly i.e.
'C:\dir\zip.xls....etc' it fails in the same way. Strange! I know there is
also an issue with using brackets "[]" and single quotes in the string, but
I've tried all combinations of these, no result.

 

[JE McGimpsey]

tnx, for your quick responce, i'm using XP with Excel 2003
I've tried your suggestions, but they give the same error "1004 : Methode
range of object_global failed'
Notice that using a local named range has no problems, only when named range
refers to another Workbook, and the VLOOKUP function has no problems with the
named range when used directly in a worksheet in either case. Also when I
don't use the named range but refer to the range directly i.e.
'C:\dir\zip.xls....etc' it fails in the same way. Strange! I know there is
also an issue with using brackets "[]" and single quotes in the string, but
I've tried all combinations of these, no result.

Is your source workbook closed? The range passed by VBA needs to be to
an open workbook.

(The reason that I asked about your version is that you're posting in a
Mac XL newsgroup)

 

[ChillyWilly]

Hi, again
I now open the workbook the range refers to, same result!

I wasn't aware I posted in the MAC group

 

[Bob Greenblatt]

Try:
x = Application.VLookup("ABX", _
['workbookname.xls'!ZipCodes].RefersToRange, 2)

 

[JE McGimpsey]

I now open the workbook the range refers to, same result!

I can't reproduce that here, depending on what route you took. If you
used Range("ZipCodes"), then the workbook containing the name has to be
active. If instead you used the fully qualified range
(ThisWorkbook.Names("ZipCodes").RefersToRange) then the range should be
returned. What happens when you enter this in the immediate window (with
"x" being your workbook name that contains the name ZipCodes):

?Workbooks("x").Names("ZipCodes").RefersToRange.Address

I wasn't aware I posted in the MAC group

Yup, that's what the "mac" in microsoft.public.mac.office.excel refers
to.

 

[ChillyWilly]

No luck Bob, "error 242 Object required"

 

[Bob Greenblatt]

No luck Bob, "error 242 Object required"

OK, email me an example and I'll show you how to make it work.

 

[ShaneDevenshire]

Hi,

Here is the syntax you need:

x = Application.WorksheetFunction.VLookup("K",
Workbooks("ZipCodeTest.xls").Sheets("Sheet1").Range("ZipCodes"), 2, False)

where K is the item you are looking up, not a range name.

now if you the range is in the current file you don't need the file
reference so the code becomes:

x = Application.WorksheetFunction.VLookup("K",
Sheets("Sheet1").Range("ZipCodes"), 2, False)

however, if the range is unique in the activeworkbook then you can write:

x = Application.WorksheetFunction.VLookup("K", Range("ZipCodes"), 2, False)

Note that the False argument may be True for you depending on whether you
are doing and exact match or not.

And since I'm beating this one up you could reduce the above to read:

x = Application.VLookup("K", Range("ZipCodes"), 2, False)

 

[ChillyWilly]

For Bob, Still the same error "range Object_global failed", what is failing
all the time is Set range(....) when it is a reference to another WB, Below
the failing lines are comments.

For Shane, Implemeted as suggested it failes : "Subscript out of range" on
the Vlookup line
--------------------------------------------------------------------------------
Public Function VBAlookup(xV As Integer) As String
Dim TabCodes As Range

VBAlookup = "!" ' Return ! on failure
On Error GoTo errh

Application.Volatile
Workbooks.Open "ExtWB.xls"

'Set TabCodes = Range("MyExtTab") ' Point to table
'VBAlookup = Application.VLookup(xV, TabCodes, 2) ' Get value

VBAlookup = Application.VLookup(xV,
Workbooks("ExtWB.Xls").Sheets("Blad1").Range("ExtTabel"), 2)

Exit Function

errh: MsgBox "Error " & Err.Number & ":" & Err.Description
End Function
----------------------------------------------------------------------------------------

 

[ShaneDevenshire]

Regarding the failure on the line that reads:

VBAlookup = Application.VLookup(xV,
Workbooks("ExtWB.Xls").Sheets("Blad1").Range("ExtTabel"), 2)

1. xV - what is this? First you said you were looking up zipcodes, it xV
is a zip code then if it's larger than about 32000 it will fail, you would
need to change the variable type to Long.

2. for safety sake you should include the fourth argument of the VLOOKUP
function until everything is working.

3. Is the workbook ExtWB.Xls open?

 

[ChillyWilly]

Shane, the code below is a simple example that reproduces the problem I
found in my actual application, in actual application the routine does a lot
more then just looking up a value from a table. In the code below:

In the line "Set TabCode = Range("MyExtTab")", is "MyExtTab" a defined name
that points to a range in the workbook ExtWB.xls, if this name is used in a
regular worksheet LOOKUP function it just works fine, using it in VBA to
define a range it fails even when the ExtWB workbook is opened, as shown in
the example.

If, however, I open ExtWB.xls manually in Excel, everything works fine, but
the the ExtWB sheets are shown as well, which I don't want.( This also
implies that parameters xV and the fourth lookup parameter "flag" are as the
should be)

I use the defined name "MyExtTab" so the definition of the link is only done
in one place(good programming practice), I don't want to duplicate the link
definition in the VBAlookup function.

I hope you continue to investigate....
Wil

 

[Bob Greenblatt]

-
You need to fully qualify the reference to the table:
Set TabCodes=workbooks("nameofWorkbook").[MyExtTab]
If the name is local to a sheet and not global to the workbook, then you
also need the sheet name in set statement.

 

[ChillyWilly]

Bob, Workbooks(..).[MyExtTab] [MyExtTab] is not a property,event or
method of the object workbooks and so says the error when I tried this.
--

 

[ChillyWilly]

Hi,
I've solved some of the lookup problem(and got another):
I did put an "open workbook" into the VBAlookup before but this didn't work!
Apparently, an excel function may not change anything but only return a
value!!!!
So the open workbook didn't work, but didn't generate an error!!
I've moved the open workbook to the Workbook_Open routine and then the code
below works!! But....

The 'Set TabCodes = Range("MyExtTab") still failes, altough this defined
name holds exactly what is used in the next "Set TabCodes.. " line, which
does work

Now the next problem: Opening the ExtWB.xls will show it to the user, which
I don't want and there is no need for it. Hiding sheets is possible but at
least ONE should be visible!?!? I see no way to hide that ExtWB.xls workbook
completly...
Who knows how????

---------------------------------------------
Public Sub workbook_Open()
Workbooks.Open "ExtWB.xls" 'Open workbook with table
Workbooks("ExtWB.xls").Sheets("Blad1").Visible = False
Workbooks("ExtWB.xls").Sheets("Blad2").Visible = False
'Workbooks("ExtWB.xls").Sheets("Blad3").Visible = False 'Last page!
Workbooks("ExtWB.XLS").Saved = True ' Never changes
ThisWorkbook.Activate '
Show me
End Sub
-------------------------------------------
Public Function VBAlookup(xV As Integer) As String
Dim TabCodes As Range

VBAlookup = "!" ' Return ! on failure
On Error GoTo errh

'Set TabCodes = Range("MyExtTab") ' Point to table

Set TabCodes = Workbooks("ExtWB.Xls").Sheets("Blad1").Range("ExtTabel")
' Point to table
VBAlookup = Application.VLookup(xV, TabCodes, 2) ' Get value
Exit Function

errh: MsgBox "Error " & Err.Number & ":" & Err.Description
End Function
--------------------------------------------

 

[Bob Greenblatt]

The problem, as I see it, and have failed to communicate properly, is the
VBA can not resolve a name on another worksheet unless a reference is made
to that sheet. What you are trying to do is grab an external reference from
a defined name. You can't do that, you must fully qualify the name to the
other sheet.

You said: Bob, Workbooks(..).[MyExtTab] [MyExtTab] is not a
property,event or method of the object workbooks and so says the error when
I tried this.

MyExtTab MUST be a defined name on the sheet. If it is a global name, then
workbooks("Myworkbook.xls").[myexttab] will return the reference of the
defined name. If the name is a local name on a sheet, then you must use:
Workbooks("bookname").sheets("sheetname").[myexttab]