#VALUE! help
 

getting the)
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?

#VALUE! help
 

you can use iserror function or iserr function.

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

On Oct 21, 12:10*am, james wrote:
getting the)
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?

#VALUE! help
 

You missed out the line of your message where you were going to tell us what
formula you are using. :-(
--
David Biddulph

james wrote:
getting the)
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?

#VALUE! help
 

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?

#VALUE! help
 

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

"Gary''s Student" wrote:

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?

#VALUE! help
 

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.
--
Gary''s Student - gsnu200908


"james" wrote:

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

"Gary''s Student" wrote:

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?

#VALUE! help
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200908


"james" wrote:

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

"Gary''s Student" wrote:

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?

#VALUE! help
 

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

On Tue, 20 Oct 2009 12:51:04 -0700, james
wrote:

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

"Gary''s Student" wrote:

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?

#VALUE! help
 

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

On Tue, 20 Oct 2009 13:06:04 -0700, james
wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200908


"james" wrote:

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

"Gary''s Student" wrote:

In general if your_formula give an error, then use:

=IF(ISERROR(your_formula),"",your_formula)
--
Gary''s Student - gsnu200908


"james" wrote:

getting the)
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?