error message in Vlookup

J

Jill

Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub
 
F

Frank Kabel

Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")
 
R

Rob Bovey

Hi Jill,

You need to use either:

Application.VLookup

or:

Application.WorksheetFunction.VLookup

The first will return an error value if the lookup fails, the second will
generate a VBA error if the lookup fails.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
J

Jill

Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub
Click to expand...

.
Click to expand...
 
F

Frank Kabel

Hi
how is ly defined?

--
Regards
Frank Kabel
Frankfurt, Germany

Jill said:
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub
Click to expand...

.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


Jill said:
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub
Click to expand...

.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

I guess I should add that Harlan Grove also has a method that opens another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy



Tom Ogilvy said:
Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


Jill said:
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

[...]
Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.
Click to expand...
here we go:
http://tinyurl.com/2c62u

Frank
 
G

Guest

Hi Jill,

I have seen this when the column is of a different data
type (i.e. Number vs Text)...
 
D

Dave Peterson

Before Harlan drops by...

His UDF opens another instance of excel, but retrieves that value from a still
closed workbook.

(Yeah, I got corrected.)

Tom said:
I guess I should add that Harlan Grove also has a method that opens another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


Jill said:
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Thanks - guess I haven't looked at it in a while - so I guess he puts a hard
coded reference to the closed workbook in the new instance of excel, gets
the value returned, then closes everything. (and does it invisibly of
course).

--
Regards,
Tom Ogilvy

Dave Peterson said:
Before Harlan drops by...

His UDF opens another instance of excel, but retrieves that value from a still
closed workbook.

(Yeah, I got corrected.)

Tom said:
I guess I should add that Harlan Grove also has a method that opens another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.
Click to expand...
Click to expand...
Click to expand...
 
J

Jill

I have been able to correct the problem by placing the
reference table on the sheet that calls the macro (an open
table) the procedure now works perfectly.
Many thanks to all who have contributed
Jill
-----Original Message-----
[...]
Also, Andy Wiggins has a method using sql.request that
Click to expand...
is supposed to
work
and if the data is in a table, it can be accessed using
Click to expand...
ADO or
perhaps the
older DAO.

I believe Frank can post a reference to previous posts
Click to expand...
on those
topics.here we go:
http://tinyurl.com/2c62u

Frank

.
Click to expand...
 
Top