Help with avoiding division by zero error in VBA

[carstenp]

I've written this code in an attempt to put a null value in the cell if the formula returns the division by zero error, but I get the 'Overflow' message when I hit the 'If' line. Any ideas why that's happening?

                                    If IsError(.Cells(SummaryRow2 + t, m) / .Cells(SummaryRow1 + t, m)) Then
                                        .Cells(SummaryRow3 + t, m).Value = Null
                                    Else
                                        .Cells(SummaryRow3 + t, m).Formula = .Cells(SummaryRow2 + t, m) / .Cells(SummaryRow1 + t, m)
                                    End If

 

[Taisho]

Maybe you have your values dimensioned as integers instead of long.

 

[carstenp]

That's not it. I tried changing them all to long and it still gives the same error message. I even tried changing the code to this and defining p as a double

                                    On Error GoTo ErrorHandler:
                                        p = .Cells(SummaryRow2 + t, m) / .Cells(SummaryRow1 + t, m)
                                        .Cells(SummaryRow3 + t, m) = p
                                    
ErrorHandler:
    If Err.Number = 11 Then
        .Cells(SummaryRow3 + t, m).Value = Null
        Resume AfterError:
    End If
AfterError:

Any help would be greatly appreciated

 

[Taisho]

If you have 0 / 0 you get overflow error, if you have x<>0 / 0 you get division by zero error. I think that the simpliest and cleanest way is just like this:

If .Cells(summaryrow1 + t, m) = 0 Then ' if denominator equals 0 then division by 0 occurs
  .Cells(summaryrow3 + t, m).Value = Null
Else
  .Cells(summaryrow3 + t, m).Formula = .Cells(summaryrow2 + t, m) / .Cells(summaryrow1 + t, m)
End If

 

[carstenp]

Ahh that's what it was. I was dividing 0/0. Thanks!