How to Return #N/A from a formula

[kayard]

Hi,

i have some VBA code in the custom-made formula
Getprice(Date;ProductCode) that fetch data from a db and return either
a number or an error (by means of a text explanation) I would like to
return #N/A when there is an error and the value otherwise. The problem
is that if i write:

Getprice = "#N/A"

excel puts in the calling cell mere text and in the sheet #N/A isn't
considered as a real NOT AVAILABLE ... Therefore if I sum that cell
with another containing a number it doesn't show #N/A but simply avoid
considering the cell containing #N/A in the calculation of the sum ...

Thanks in advance for your help

Sorry for my bad english

Paolo

Italy

 

[Niek Otten]

Ho Paolo,

CVErr(xlErrNA)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hi,
|
| i have some VBA code in the custom-made formula
| Getprice(Date;ProductCode) that fetch data from a db and return either
| a number or an error (by means of a text explanation) I would like to
| return #N/A when there is an error and the value otherwise. The problem
| is that if i write:
|
| Getprice = "#N/A"
|
| excel puts in the calling cell mere text and in the sheet #N/A isn't
| considered as a real NOT AVAILABLE ... Therefore if I sum that cell
| with another containing a number it doesn't show #N/A but simply avoid
| considering the cell containing #N/A in the calculation of the sum ...
|
| Thanks in advance for your help
|
| Sorry for my bad english
|
| Paolo
|
| Italy
|
|
| --
| kayard
| ------------------------------------------------------------------------
| kayard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24716
| View this thread: http://www.excelforum.com/showthread.php?threadid=569327
|

 

[NickHK]

Check out CVErr function in the help and various built-in xlErr Enum values:
e.g.

Public Function GetPrice() As Variant
GetPrice = CVErr(xlErrNA)
End Function

NickHK