User Defined function - second posting
 

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

User Defined function - second posting
 

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

User Defined function - second posting
 

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

User Defined function - second posting
 

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

User Defined function - second posting
 

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