Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|