"Madiya" wrote:
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....


"Madiya" wrote:
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)")


"Madiya" wrote:
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