#VALUE! help

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

 

[muddan madhu]

you can use iserror function or iserr function.

=if(iserr(your formula),"",your formula)

 

[Gary''s Student]

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)

 

[james]

i dont think its an error, just that unused fields are not filled in.

 

[Gary''s Student]

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.

 

[james]

any way to do this in bulk? as in 100 in one go?
loads of different forulas in the sheet.

 

[Gord Dibben]

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

 

[Gord Dibben]

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