Check for #NA in 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

Check for #NA in 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 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

Check for #NA in 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()...

Check for #NA in VB?
 

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()...

Check for #NA in VB?
 

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

Check for #NA in 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

Check for #NA in VB?
 

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

Check for #NA in VB?
 

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

Check for #NA in VB?
 

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