| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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()... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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()... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
| Loop through column(s) to check values, perform action based on check | Excel Programming | |||
| Increase size of a Forms Check Box (click on to enter check mark) | Excel Discussion (Misc queries) | |||
| Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
| Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
Linear Mode
