This has be posted before. I received a couple of replies but unfortunately
the proposed solutions didn't work so I'm posting again.

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 defined
(i.e. VLookup).

Is there something I'm missing from this?

Thanks

I had a typo in my previous response and didn't assign the results to the
name of the function. This has been tested and works when the workbook
Data.xls is open.

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")

res = Application.VLookup(Str_Deal, Decap_Range, 6, False)
If Not IsError(res) Then
Pcode_na = res
Else
Pcode_na = 0
End If
End Function

--
Regards,
Tom Ogilvy

"PC" <paulm DOT c at iol DOT ie wrote in message
...
This has be posted before. I received a couple of replies but
unfortunately
the proposed solutions didn't work so I'm posting again.

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 defined
(i.e. VLookup).

Is there something I'm missing from this?

Thanks

PC,

Check for the error, and change the return to 0 if it exists:

Function Pcode_na(Str_Deal As Variant) As Variant
'Variable to hold VLookup Range
Dim Decap_Range As Range

'Constant Range
Decap_Range = Workbooks("Data.xls").Sheets("Decap").Range("A3:G5 000")

Pcode_na = Application.VLookup(Str_Deal, Decap_Range, 6, False)

'This is the check that you need
If IsError(Pcode_na) Then Pcode_na = 0

End Function

HTH,
Bernie
MS Excel MVP

"PC" <paulm DOT c at iol DOT ie wrote in message
...
This has be posted before. I received a couple of replies but
unfortunately
the proposed solutions didn't work so I'm posting again.

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 defined
(i.e. VLookup).

Is there something I'm missing from this?

Thanks

Thanks for your help guys. Both solutions work great. One question: When I
use Application.WorksheetFunction.VLookup the returned value is fine if
there is no error but when there is an error the result is #Value. When I
remove the WorkSheetFunction to leave Application.VLookup as you suggested
all works fine.

When, if ever, is WorkSheetFuntion required.

Again thats for your help.

Paul


"PC" <paulm DOT c at iol DOT ie wrote in message
...
This has be posted before. I received a couple of replies but
unfortunately
the proposed solutions didn't work so I'm posting again.

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 = Pcode = Application.WorksheetFunction.VLookup(Str_Deal,
Decap_Range, 6,
False)
(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 defined
(i.e. VLookup).

Is there something I'm missing from this?

Thanks

When worksheetfunction is used, Vlookup returns a 1004 type error - which is
suppressed when used in a UDF.

Using just application, it returns the equivalent of #N/A which can be
checked with iserror. It is your choice which way you go. You just have to
write the code to recognize the condition. (for Worksheetfunction you would
use a normal VBA error handler).

--
Regards,
Tom Ogilvy

"PC" <paulm DOT c at iol DOT ie wrote in message
...
Thanks for your help guys. Both solutions work great. One question: When I
use Application.WorksheetFunction.VLookup the returned value is fine if
there is no error but when there is an error the result is #Value. When I
remove the WorkSheetFunction to leave Application.VLookup as you suggested
all works fine.

When, if ever, is WorkSheetFuntion required.

Again thats for your help.

Paul


"PC" <paulm DOT c at iol DOT ie wrote in message
...
This has be posted before. I received a couple of replies but
unfortunately
the proposed solutions didn't work so I'm posting again.

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 = Pcode = Application.WorksheetFunction.VLookup(Str_Deal,
Decap_Range, 6,
False)
(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 defined
(i.e. VLookup).

Is there something I'm missing from this?

Thanks