Change #DIV/0! across a range

[ghobbit]

Hi

I had a problem with a #DIV/0! error and was given some code here that
works very well on the cell it is aimed at, but I was wondering how I
could tweak it so that it would work across a range.

The code is this

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

As I say this works well, however I have 10 or more different
calculations using variations on the above formula any one of which
could result in the denominator equaling zero. The answer to each of
those calculations are put into a different cell ranging from AL9 to
AS9. I could paste the code above 10times changing the different cell
values in the calculations but is there a simple one line or two piece
of code that will allow me to apply the above formula to a range of
cells in one go. That is it performs all its calculations and then runs
the next bit of code that checks the range AL9 to AS9 and if it finds a
#DIV/0! in the cell it'll change it to 0 (preferably) or even blank.

I've looked at

Range("AF9:AL9").Value = Evaluate("IF(AE9+AC9=0,0,SUM((AE9
/(AE9+AC9))*100))")

Which will put zero in that range but only if AE9+AC9=0 and as I say
each cell contents is based on a different calculation. I've looked at
ERROR.TYPe or IsError type things and I'm just not getting anywhere.

Any help would be greatly appreciated

regards

Steve

 

[Tom Ogilvy]

It isn't clear what changes and what stays the same as you move across the
row.

Also, is there a reason to use evaluate or would you be just as happing
putting the formula itself in the cell


Range("AF9:AL9").Formula = "=If(Y9+W9=0,0,Z9/(Y9+W9))"

? range("AL9").Formula
=IF(AE9+AC9=0,0,AF9/(AE9+AC9))
? range("AF9").Formula
=IF(Y9+W9=0,0,Z9/(Y9+W9))

 

[Tom Ogilvy]

Oops,
had one wrong cell address:

Range("AF9:AL9").Formula = "=If(Y9+W9=0,0,Y9/(Y9+W9))"
? range("AL9").Formula
=IF(AE9+AC9=0,0,AE9/(AE9+AC9))
? range("AF9").Formula
=IF(Y9+W9=0,0,Y9/(Y9+W9))

 

[ghobbit]

Hi Tom

Thanks for your reply
I have cells in the range AF9:AL9. Each cell may or may not contain the
error #DIV/0! depending on the result of the calculation. Once the
calculations have been performed I'd like the code to be able to look
at each individual cell in that range to determine if it contains an
error such as #DIV/0! and if so change it to '0'. If the cell contains
a valid integer then the code will leave it alone and move onto the
next cell in the range.


Sorry for showing my ignorance but I dont understand where the W9 and
Y9 etc comes in. and the lines starting with a question mark - is that
part of the code?

At the moment I have a command button that on clicking works out a lot
of calculations and does so in a certain order. The results of some of
those calculations unfortunately results in zero so when that result
gets used as part of another calculation I get the error code.

This is part of the code I have at the moment and I'm sure theres
probably a better way to do it but I have to keep it simple as I'm not
very conversant with VBA and I need to be able to follow whats going on
- it works and thats what matters to me. I just need to tidy up those
#DIV/0! erros if they appear.

Cells(2, "AF").Value = Evaluate("=SUM((AE10 / (AE10+AC10))*100)") 'Lab
sensitivity
Cells(2, "AG").Value = Evaluate("=SUM((U10 / (U10+Z10))*100)") 'Lab
specificity
Cells(2, "AH").Value = Evaluate("=SUM((AC10 / (AC10+AE10))*100)") 'Lab
FN rate
Cells(2, "AI").Value = Evaluate("=SUM((Z10 / (U10+Z10))*100)") 'Lab FP
Rate
Cells(2, "AJ").Value = Evaluate("=SUM((AE10 / (AE10+Z10))*100)") 'Lab
PPV


Hope this makes it a bit clearer what I'm trying to do

regards

Steve