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

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