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. 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

[Tom Ogilvy]

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


--
Regards,
Tom Ogilvy


"ghobbit" <ghobbit.28acvm_1148430302.0175@excelforum-nospam.com> wrote in
message news:ghobbit.28acvm_1148430302.0175@excelforum-nospam.com...
>
> 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
>

[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" <ghobbit.28acvm_1148430302.0175@excelforum-nospam.com> wrote
in message news:ghobbit.28acvm_1148430302.0175@excelforum-nospam.com...
>
> 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
>

[ghobbit]

Hi

Both of those solutions worked - thanks very much

steve