div0 error

[jillseurre]

is there any way to set a worksheet to not display div0.

i have a reference sheet with 100's of inputs alot will not be populated. on a result sheet a i have a lot of calcs - and because the ref sheet is not populated i get div0.

i dont want to have to re-enter a formula for errors.

if i have to redo all the formulas - whats the best code for handling all types of error.

 

[dyarwood]

You could do a find and replace. Find the DIV0 and replace with nothing. That should just get rid of them all.

dyarwood

 

[jillseurre]

i have formuals in all those cells and want to keeo the fomulas

 

[dyarwood]

You could do this with formulas using an if statement, but any code you write to do it would overwrite any formulas in the cells.

Is it the same formula for each cell in a column/row and is the spreadsheet in the same layout and size all the time. if so you can get a code to do it all.

dyarwood

 

[xlbo]

If your formula is, for example

=Sheet2!A1/Sheet1!A1

and you get a DIV/0 error because there is no data in sheet1, try this

for each c in activesheet.usedrange
if c.hasformula = true then
myForm = right(c.formula,len(c.formula)-1)
c.formula = "=If(ISERROR(" & myForm & &quot,""""," & myForm & &quot"
else
end if
next


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sawedoff]

An easy way to solve the problem would be to apply conditional formatting to the result cells, that looks to see if the cell containing the divisor is zero, and formatting the result with white font (so it doesn't show).

Example: if A1 is 24, and B1 is 0, and C1 is A1/B1, then you would put conditional formatting in C1 to say:
(Condition) Formula is =B1=0

The only problem here is that you can't use cell C1 in any further calculations, since it still contains the error, but just doesn't show.

Sawedoff