Check for #NA in VB?

  • Thread starter Thread starter fedude
  • Start date Start date
F

fedude

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
 
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
Click to expand...

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
 
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
 
Look again. You may be surprised.
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
Click to expand...
 
OOPS. ISNA() is available. It just didn't show up in my drop-down function
list. NA() is not available.
Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation List (conditional) 2
Distinguishing 0 formatted as "na" and as 0 2
Urgent 2
colour format text when copied from text box 4
Max 5
Date subtraction 1
Excel Named Ranges and Conditional Format 3
Enter NA() into All Blank Cells in Range 16

Back
Top