Is there a way I can check to see if a cells formula resolves to #NA. It
doesn't appear that ISNA or NA() are available in excel vb

You can use certain worksheet functions directly in VBA, if there is
no VBA equivalent. Just preface them with "Worksheetfunction" for
example

Sub Clear_ISNA()
Dim cell As Excel.Range

Application.ScreenUpdating = False

For Each cell In Selection
If WorksheetFunction.IsNA(cell) = True Then cell.ClearContents
Next cell

Application.ScreenUpdating = True

End Sub


HTH,
JP

On Apr 10, 4:55*pm, fedude <fed...@discussions.microsoft.com> wrote:
> Is there a way I can check to see if a cells formula resolves to #NA. * It
> doesn't appear that ISNA or NA() are available in excel vb

> Is there a way I can check to see if a cells formula resolves to #NA. It
> doesn't appear that ISNA or NA() are available in excel vb

use WorksheetFunction.IsNA()...

ISNA() is not available as a worksheet function in VB.

"IanKR" wrote:

> > Is there a way I can check to see if a cells formula resolves to #NA. It
> > doesn't appear that ISNA or NA() are available in excel vb
>
> use WorksheetFunction.IsNA()...
>
>

I'm pretty sure that ISNA() is not avaialble as a worksheet function in VB.
I need an alternative.

application.worksheetfunction.isna() <== does not exist in VB

"JP" wrote:

> You can use certain worksheet functions directly in VBA, if there is
> no VBA equivalent. Just preface them with "Worksheetfunction" for
> example
>
> Sub Clear_ISNA()
> Dim cell As Excel.Range
>
> Application.ScreenUpdating = False
>
> For Each cell In Selection
> If WorksheetFunction.IsNA(cell) = True Then cell.ClearContents
> Next cell
>
> Application.ScreenUpdating = True
>
> End Sub
>
>
> HTH,
> JP
>
> On Apr 10, 4:55 pm, fedude <fed...@discussions.microsoft.com> wrote:
> > Is there a way I can check to see if a cells formula resolves to #NA. It
> > doesn't appear that ISNA or NA() are available in excel vb
>
>

You could also look at what's displayed in the cell

if lcase(somerange.text) = lcase("#n/a") then

or if you're satisfied just looking for any old error:

if iserror(somerange.value) then


fedude wrote:
>
> Is there a way I can check to see if a cells formula resolves to #NA. It
> doesn't appear that ISNA or NA() are available in excel vb

--

Dave Peterson

Look again.

fedude wrote:
>
> ISNA() is not available as a worksheet function in VB.
>
> "IanKR" wrote:
>
> > > Is there a way I can check to see if a cells formula resolves to #NA. It
> > > doesn't appear that ISNA or NA() are available in excel vb
> >
> > use WorksheetFunction.IsNA()...
> >
> >

--

Dave Peterson

Look again. You may be surprised.

fedude wrote:
>
> I'm pretty sure that ISNA() is not avaialble as a worksheet function in VB.
> I need an alternative.
>
> application.worksheetfunction.isna() <== does not exist in VB
>
> "JP" wrote:
>
> > You can use certain worksheet functions directly in VBA, if there is
> > no VBA equivalent. Just preface them with "Worksheetfunction" for
> > example
> >
> > Sub Clear_ISNA()
> > Dim cell As Excel.Range
> >
> > Application.ScreenUpdating = False
> >
> > For Each cell In Selection
> > If WorksheetFunction.IsNA(cell) = True Then cell.ClearContents
> > Next cell
> >
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> >
> > HTH,
> > JP
> >
> > On Apr 10, 4:55 pm, fedude <fed...@discussions.microsoft.com> wrote:
> > > Is there a way I can check to see if a cells formula resolves to #NA. It
> > > doesn't appear that ISNA or NA() are available in excel vb
> >
> >

--

Dave Peterson

OOPS. ISNA() is available. It just didn't show up in my drop-down function
list. NA() is not available.
Thanks!

"Dave Peterson" wrote:

> Look again. You may be surprised.
>
> fedude wrote:
> >
> > I'm pretty sure that ISNA() is not avaialble as a worksheet function in VB.
> > I need an alternative.
> >
> > application.worksheetfunction.isna() <== does not exist in VB
> >
> > "JP" wrote:
> >
> > > You can use certain worksheet functions directly in VBA, if there is
> > > no VBA equivalent. Just preface them with "Worksheetfunction" for
> > > example
> > >
> > > Sub Clear_ISNA()
> > > Dim cell As Excel.Range
> > >
> > > Application.ScreenUpdating = False
> > >
> > > For Each cell In Selection
> > > If WorksheetFunction.IsNA(cell) = True Then cell.ClearContents
> > > Next cell
> > >
> > > Application.ScreenUpdating = True
> > >
> > > End Sub
> > >
> > >
> > > HTH,
> > > JP
> > >
> > > On Apr 10, 4:55 pm, fedude <fed...@discussions.microsoft.com> wrote:
> > > > Is there a way I can check to see if a cells formula resolves to #NA. It
> > > > doesn't appear that ISNA or NA() are available in excel vb
> > >
> > >
>
> --
>
> Dave Peterson
>