UDF not working for VLookup

[Madiya]

I am trying to write a UDF but it does not work.
Here is my simple code, what am I doing wrong?

===================
Public Function PP(rng)
Dim WB As Workbook

Set WB = Workbooks("Code Master.xls")
MsgBox WB.Sheets(1).Range("A10").Value
Set VLRNG = WB.Sheets("SHEET1").Range("B:K")
'PP = Application.WorksheetFunction.VLookup(rng.Value, VLRNG, 4, 0)
'<<<not working
PP = Evaluate(Application.WorksheetFunction.VLookup(rng, VLRNG, 4,
0))'<<<not working

End Function
==================

Pl help.

Regards,

 

[Vacuum Sealed]

I am trying to write a UDF but it does not work.
Here is my simple code, what am I doing wrong?

===================
Public Function PP(rng)
Dim WB As Workbook

Set WB = Workbooks("Code Master.xls")
MsgBox WB.Sheets(1).Range("A10").Value
Set VLRNG = WB.Sheets("SHEET1").Range("B:K")
'PP = Application.WorksheetFunction.VLookup(rng.Value, VLRNG, 4, 0)
'<<<not working
PP = Evaluate(Application.WorksheetFunction.VLookup(rng, VLRNG, 4,
0))'<<<not working

End Function
==================

Pl help.

Regards,

hi

Try

PP = Evaluate("=VLookup(rng, VLRNG, 4, >0)")

HTH
Mick.

 

[joeu2004]

I am trying to write a UDF but it does not work.
Here is my simple code, what am I doing wrong?
===================
Public Function PP(rng)
Dim WB As Workbook
Set WB = Workbooks("Code Master.xls")
MsgBox WB.Sheets(1).Range("A10").Value
Set VLRNG = WB.Sheets("SHEET1").Range("B:K")
'PP = Application.WorksheetFunction.VLookup(rng.Value, VLRNG, 4, 0)
'<<<not working
PP = Evaluate(Application.WorksheetFunction.VLookup(rng, VLRNG, 4,
0))'<<<not working
End Function

The syntax of the first form should work, but only if PP is called with a
cell reference, e.g. =PP(A1). Change rng.Value to simply rng.

Note-1: I would change the name rng to myval, and I would use myval in
place rng.Value in the VLookup call.

Note-2: I would use Application.Vlookup instead of
WorksheetFunction.VLookup if the lookup might fail. The Application.Vlookup
returns an Excel error if VLookup fails instead of aborting the VBA function
with an error. Alternatively, you might use an On Error statement.

Ostensibly, the Evaluate syntax should be:

PP = Evaluate("vlookup(" & rng & ",'[Code Master.xls]Sheet1'!B:K,4,0)")

However, that will not work if the value of rng might be non-numeric. It
gets really complicated if you want to handle all possibilities.

Note-3: In all cases, it is "bad practice" to use a lookup table of the
form B:K when doing a linear lookup, unless you know that the lookup will
succeed. It would be better to limit the lookup table range, e.g.
B10000:K10000. Otherwise, Excel 2003 will search 65536 rows, and Excel 2007
and later will search 1+ million rows.

 

[joeu2004]

Ostensibly, the Evaluate syntax should be:
PP = Evaluate("vlookup(" & rng & ",'[Code Master.xls]Sheet1'!B:K,4,0)")

That syntax as well as your original design assume that "Code Master.xls" is
already open in the same Excel instance.

 

[joeu2004]

Set WB = Workbooks("Code Master.xls")
MsgBox WB.Sheets(1).Range("A10").Value
Set VLRNG = WB.Sheets("SHEET1").Range("B:K") [....]
PP = Evaluate(Application.WorksheetFunction.VLookup(rng, VLRNG, 4,
0))'<<<not working

[....]
Ostensibly, the Evaluate syntax should be:
PP = Evaluate("vlookup(" & rng & ",'[Code Master.xls]Sheet1'!B:K,4,0)")

If your purposeful intent is to use a variable reference like VLRNG, the
syntax would be:

PP = Evaluate("vlookup(" & rng & "," & VLRNG.Address(external:=True) &
",4,0)")

The point is: the parameter of Evaluate is a __string__ whose value is a
formula as it would appear in Excel.

 

[Madiya]

Mike,
Thanks but it didnt worked. It gives error 2015 in debug mode. But I
am checked and lookup value is there in the code master file.

joeu2004,
Thanks a lot to have a look on my post and providing your valuable
suggetions.

I have tried all variations out of which only following works.
PP = Application.VLookup(myval, VLRNG, 4, 0) ' working
I will try to incorporate all your comments.
Most of the data in code master is numbers stored as text against
which I want the data of part no, cls, plate etc with the help of
vllokup.

That syntax as well as your original design assume that "Code Master.xls" is
already open in the same Excel instance.

Sure currently it is open but my target is to get the vlookup data
without opening the file.

Is it possible to get result without opening code master file?
If I put the UDF in add in, then is it possible?


Regards,
Madiya

 

[joeu2004]

I have tried all variations out of which only following works.
PP = Application.VLookup(myval, VLRNG, 4, 0)

That is probably the more efficient, as well. But FYI....

Most of the data in code master is numbers stored as text against
which I want the data of part no, cls, plate etc with the help of
vllokup.

In that case, the Evaluate form might be written as follows:

PP = Evaluate("vlookup(" & Chr(34) & myval & Chr(34) & ",'[Code
Master.xls]Sheet1'!B:K,4,0)")

Or if you prefer:

PP = Evaluate("vlookup(""" & myval & """,'[Code
Master.xls]Sheet1'!B:K,4,0)")

Is it possible to get result without opening code master file?

Not in a UDF, to be sure. UDFs are not permitted to modify Excel state
(modify other cells, add worksheets, open workbooks, etc).

But apparently not even in a subroutine. I thought the Evaluate form might
work (with some tweaks). But my experiments suggest it does not. I am
surprised, since =VLOOKUP(A1,'[Code Master.xls]Sheet1'!B:K,4,0) works
without opening 'Code Master.xls'.

(That shorthand form works only if 'Code Master.xls' is in the "current"
folder. Normally we write a complete code path of the form
=VLOOKUP(A1,'C:\Documents and Settings\joeu2004\My Documents\[Code
Master.xls]Sheet1'!B:K,4,0). In fact, Excel will convert the shorthand form
above to this longhand form.)

Of course, you could do something like the following in a macro:

Dim myOpen As Boolean, wb As Workbook
On Error Resume Next
myOpen = False
Set wb = Workbooks("Code Master.xls")
If Err <> 0 Then
Err.Clear
Set wb = Workbooks.Open("C:\Documents and Settings\joeu2004\My
Documents\Code Master.xls")
If Err <> 0 Then Exit Sub
myOpen = True
End If
On Error GoTo 0
'....rest of your code....
If myOpen Then wb.Close