Help with the old #DIV/0! chesnut
 

Hi

I'm having some problems with the #DIV/0! and how to get rid of it.
know why its occuring.

I have a line of code which says

Cells(9, "AF").Value = Evaluate("=SUM((AE9 / (AE9+AC9))*100)")

Now this will sometimes return 0 so what I would like is instead o
cell AF:9 being populated with #DIV/0!, I would like either the cell t
read actual 0 or be left blank - I dont care which.

I've tried this but think I'm going astray

Cells(9, "AF").Value = IF(ISERROR(Evaluate("=SUM((AE9
(AE9+AC9))*100)")),"",Evaluate("=SUM((AE9 / (AE9+AC9))*100)"))

But I get a Compile error : expected expression with the I
highlighted. Could someone show me the error of my ways?

Also whilst we're on it what if I wanted the same thing over a whol
range of cells. To put it in words I have cells AF3:AL9 all of whic
are populated based on the result of a calculation like the above
however the calculations are all different. So how would I write i
code "if any cell within AF3:AL9 = #DIV/0! Then "" or "0".

any help would be most appreciated

regards

Stev

--
ghobbi
-----------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...fo&userid=1238
View this thread: http://www.excelforum.com/showthread.php?threadid=54492

Help with the old #DIV/0! chesnut
 

Cells(9, "AF").Value = Evaluate("IF(AE9+AC9=0,0,SUM((AE9 /
(AE9+AC9))*100))")


--
Regards,
Tom Ogilvy


"ghobbit" wrote in
message ...

Hi

I'm having some problems with the #DIV/0! and how to get rid of it. I
know why its occuring.

I have a line of code which says

Cells(9, "AF").Value = Evaluate("=SUM((AE9 / (AE9+AC9))*100)")

Now this will sometimes return 0 so what I would like is instead of
cell AF:9 being populated with #DIV/0!, I would like either the cell to
read actual 0 or be left blank - I dont care which.

I've tried this but think I'm going astray

Cells(9, "AF").Value = IF(ISERROR(Evaluate("=SUM((AE9 /
(AE9+AC9))*100)")),"",Evaluate("=SUM((AE9 / (AE9+AC9))*100)"))

But I get a Compile error : expected expression with the IF
highlighted. Could someone show me the error of my ways?

Also whilst we're on it what if I wanted the same thing over a whole
range of cells. To put it in words I have cells AF3:AL9 all of which
are populated based on the result of a calculation like the above,
however the calculations are all different. So how would I write in
code "if any cell within AF3:AL9 = #DIV/0! Then "" or "0".

any help would be most appreciated

regards

Steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544920

Help with the old #DIV/0! chesnut
 

One way

tmp = Evaluate("=SUM((AE9 / (AE9+AC9))*100)")
If IsError(tmp) Then
Cells(9, "AF").Value = 0
Else
Cells(9, "AF").Value = tmp
End If

keizi

"ghobbit" wrote
in message ...

Hi

I'm having some problems with the #DIV/0! and how to get rid of it. I
know why its occuring.

I have a line of code which says

Cells(9, "AF").Value = Evaluate("=SUM((AE9 / (AE9+AC9))*100)")

Now this will sometimes return 0 so what I would like is instead of
cell AF:9 being populated with #DIV/0!, I would like either the cell
to
read actual 0 or be left blank - I dont care which.

I've tried this but think I'm going astray

Cells(9, "AF").Value = IF(ISERROR(Evaluate("=SUM((AE9 /
(AE9+AC9))*100)")),"",Evaluate("=SUM((AE9 / (AE9+AC9))*100)"))

But I get a Compile error : expected expression with the IF
highlighted. Could someone show me the error of my ways?

Also whilst we're on it what if I wanted the same thing over a whole
range of cells. To put it in words I have cells AF3:AL9 all of which
are populated based on the result of a calculation like the above,
however the calculations are all different. So how would I write in
code "if any cell within AF3:AL9 = #DIV/0! Then "" or "0".

any help would be most appreciated

regards

Steve


--
ghobbit
----------------------------------------------------------------------
--
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread:
http://www.excelforum.com/showthread...hreadid=544920

Help with the old #DIV/0! chesnut
 

Hi

Both of those solutions worked - thanks very much

steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544920