VBA and #N/A

[rvExcelNewTip]

I can check whether a cell has a value of #N/A by using
WorksheetFunction.IsNa(CellAddress).

But can I put a #N/A value into a cell? There seems to be no equivalent of the
NA() function that you can use directly in a worksheet. Something like
Range(CellAddress).Value=NA() ???

Putting the string equivalent "#N/A" into the cell doesn't help, because the spreadsheet has to function in a multilanguage environment.

[JE McGimpsey]

One way:

Range(CellAddress).Value = CVErr(xlErrNA)

In article
<[email protected]>,
rvExcelNewTip
<[email protected]> wrote:

> I can check whether a cell has a value of #N/A by using
> WorksheetFunction.IsNa(CellAddress).
>
> -But can I put a #N/A value into a cell?- There seems to be no
> equivalent of the
> NA() function that you can use directly in a worksheet. Something
> like
> Range(CellAddress).Value=NA() ???
>
> Putting the string equivalent "#N/A" into the cell doesn't help,
> because the spreadsheet has to function in a multilanguage environment.

[Dave Peterson]

Maybe you could try:

with range(celladdress)
.formula = "=na()"
.value = .value
end with

But I bet this would work, too:

range(celladdress).value = cverr(xlerrna)

rvExcelNewTip wrote:
>
> I can check whether a cell has a value of #N/A by using
> WorksheetFunction.IsNa(CellAddress).
>
> -But can I put a #N/A value into a cell?- There seems to be no
> equivalent of the
> NA() function that you can use directly in a worksheet. Something
> like
> Range(CellAddress).Value=NA() ???
>
> Putting the string equivalent "#N/A" into the cell doesn't help,
> because the spreadsheet has to function in a multilanguage environment.
>
> --
> rvExcelNewTip
> ------------------------------------------------------------------------
> rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
> View this thread: http://www.excelforum.com/showthread...hreadid=376276

--

Dave Peterson

[rvExcelNewTip]

CVErr(xlErrNA) works like a charm. Thanks!