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