Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
On 3/07/2012 12:21 AM, Madiya wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
"Madiya" wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
PS.... I wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
PPS.... I wrote:
"Madiya" wrote: [....] 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not working for VLookup
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Not working | Excel Worksheet Functions | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLOOKUP NOT WORKING | Excel Discussion (Misc queries) | |||
Vlookup no working | New Users to Excel | |||
Vlookup still not working | Excel Programming |