#DIV/0!

[Rahul]

I am trying to create a few command lines to scan a
worksheet and if any of the cells that has a division
function in it has a 0 in the numerator or denominator,
then replace that cell with just a 0.. if not then perform
the division. How do I do that with If/Else statement?
This is what I have so far which is thoroughly wrong:

If (num = 0 Or denom = 0) Then ActiveCell.FormulaR1C1
= "0" Else perform division End If

Please help!

Thanks so much!

 

[JE McGimpsey]

I am trying to create a few command lines to scan a
worksheet and if any of the cells that has a division
function in it has a 0 in the numerator or denominator,
then replace that cell with just a 0.. if not then perform
the division. How do I do that with If/Else statement?
This is what I have so far which is thoroughly wrong:

If (num = 0 Or denom = 0) Then ActiveCell.FormulaR1C1
= "0" Else perform division End If

Well, you can use a macro, but you'd be far better off eliminating the
errors altogether, as well as eliminating the need for a macro, by
changing your formulae from

=num/denom

to

=IF(denom=0, 0, num/denom)

Note that you don't need to test the numerator at all.

To do it with a macro (Note: this replaces your formula with a value -
if the denominator changes to non-zero, the cell will still contain 0):

Public Sub ReplaceDiv0s()
Dim vDiv0Err As Variant
Dim rErrors As Range
Dim rCell As Range

vDiv0Err = CVErr(xlErrDiv0)
On Error Resume Next
Set rErrors = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not rErrors Is Nothing Then
For Each rCell In rErrors
If rCell.Value = vDiv0Err Then _
rCell.Value = 0
Next rCell
End If
End Sub