#VALUE! help

  • Thread starter Thread starter james
  • Start date Start date
J

james

getting there:)
what about stopping a cell saying; #VALUE!
i have about 100 clees doing this when unused rows are left blank, any way
of doing this in bulk?
 
you can use iserror function or iserr function.

=if(iserr(your formula),"",your formula)
 
In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
 
You are correct. Although its not a REAL error, its enough to screw-up and
ADD or AVERAGE across a range of cells. That's why we should mask it out.
 
It is an error.

One caveat about using ISERROR function.

It hides all errors which may not be desirable.

Best to trap for the blank cells.

Post your formulas which return the error.


Gord Dibben MS Excel MVP
 
Should you decide to use ISERROR on your formulas you can do it in bulk with
this macro.

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


Gord Dibben MS Excel MVP
 
Back
Top