Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function
Hi I have a UDF that is working fine:
Function Pcode_na(Str_Deal As Variant) 'Variable to hold VLookup Range Dim Decap_Range As Variant 'Constant Range Decap_Range = Workbooks("Data.xls").Sheets("Decap").Range("A3:G5 000") 'User Defined Function Pcode = Application.WorksheetFunction.VLookup(Str_Deal, Decap_Range, 6, False) I'm trying to extend this so it takes into account results that are returned n/a and replace this with "0" I have tried: 'User Defined Function Pcode = Application.WorksheetFunction.if(IsNA(VLookup(Str_ Deal, Decap_Range, 6, False)), 0, (VLookup(Str_Deal, Decap_Range, 6, False))) When I run this I get and error stating that the function is not define (i.e. VLookup). Is there something I'm missing from this? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function
Function Pcode_na(Str_Deal As Variant)
'Variable to hold VLookup Range Dim Decap_Range As Range Dim res as Variant 'Constant Range set Decap_Range = Workbooks("Data.xls").Sheets("Decap").Range("A3:G5 000") 'User Defined Function res = Application.VLookup(Str_Deal, Decap_Range, 6, False) if not iserror(res) then pcode = res else pcode = 0 End if End Function -- Regards, Tom Ogilvy "PC" <paulm DOT c at iol DOT ie wrote in message ... Hi I have a UDF that is working fine: Function Pcode_na(Str_Deal As Variant) 'Variable to hold VLookup Range Dim Decap_Range As Variant 'Constant Range Decap_Range = Workbooks("Data.xls").Sheets("Decap").Range("A3:G5 000") 'User Defined Function Pcode = Application.WorksheetFunction.VLookup(Str_Deal, Decap_Range, 6, False) I'm trying to extend this so it takes into account results that are returned n/a and replace this with "0" I have tried: 'User Defined Function Pcode = Application.WorksheetFunction.if(IsNA(VLookup(Str_ Deal, Decap_Range, 6, False)), 0, (VLookup(Str_Deal, Decap_Range, 6, False))) When I run this I get and error stating that the function is not define (i.e. VLookup). Is there something I'm missing from this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
User Defined Function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |