Help with the old #DIV/0! chesnut

[ghobbit]

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

 

[Tom Ogilvy]

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

 

[kounoike]

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]

Hi

Both of those solutions worked - thanks very much

steve