Make n/a go away

[MacScott]

I remember from my Lotus 123 days that you could tell lotus to supres
error messages thus a cell with n/a would appear empty and when th
formula had something to do, that answer would show up.

I am making a spreadsheet with multiple instances of formulas waitin
on data and would like for all those n/a to go away with out going i
and editing every formlula with iserror checking.

Can we do this?

 

[Dave R.]

You can use conditional formatting.

Select cell or range, go to format>conditonal formatting. Select "formula
is" and enter =iserror(b1)

where B1 is whatever cell you're in. Then you can select the format to be
white font (if you have white background), so you wont see it. Then you can
copy it elsewhere by coping/paste special formats.

 

[Ken Wright]

=IF(ISNA(your_formula),"",your_formula)

 

[Gord Dibben]

If you have many formulas to change use this code.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Gord Dibben Excel MVP