Compound If Statement and VBA

[jswhip]

Within a macro, how does one check for division by zero? I know there are instances where I will get a division by zero error, thus I want to check for that error proceeding. I know how to perform the check if I have am using a formula within a cell, but don't the syntax for using it within a formula.

For example, in the following code, I can't put a check for a zero divisor as a condition for the division operation (eg - "Cells(x, 7) / Cells(x, 6)").


If Cells(x, 23) > 5000 Or Cells(x, 24) > 0 Then
Cells(x, FinalCol + 1) = "IntPrin"
ElseIf Cells(x, 7) / Cells(x, 6) < 0.8 Or _
Cells(x, 11) - Cells(x, 10) > 0 Or _
(Cells(x, 33) = "Y" Or Cells(x, 34) = "Y" Or _
Cells(x, 35) = "Y" Or Cells(x, 36) = "Y" Or _
Cells(x, 37) = "Y" Or Cells(x, 38) = "Y" Or _
Cells(x, 39) = "Y" Or Cells(x, 40) = "Y") Then
Cells(x, FinalCol + 1) = "ProbSubCon"

End If

 

[sjeter]

Try using something like this

ElseIf Not IsError(Cells(x, 7) / Cells(x, 6)) AND Cells(x, 7) / Cells(x, 6)) < 0.8

 

[shg]

You could rewrite it to avoid the division:

    If Cells(x, 23) > 5000 Or _
       Cells(x, 24) > 0 Then
        Cells(x, FinalCol + 1) = "IntPrin"
    ElseIf Cells(x, 7) < Cells(x, 6) * 0.8 Or _
           Cells(x, 11) > Cells(x, 10) Or _
           Cells(x, 33) = "Y" Or _
           Cells(x, 34) = "Y" Or _
           Cells(x, 35) = "Y" Or _
           Cells(x, 36) = "Y" Or _
           Cells(x, 37) = "Y" Or _
           Cells(x, 38) = "Y" Or _
           Cells(x, 39) = "Y" Or _
           Cells(x, 40) = "Y" Then
        Cells(x, FinalCol + 1) = "ProbSubCon"
    End If

Or, more briefly,

    If Cells(x, 23) > 5000 Or _
       Cells(x, 24) > 0 Then
        Cells(x, FinalCol + 1) = "IntPrin"
    ElseIf Cells(x, 7) < Cells(x, 6) * 0.8 Or _
           Cells(x, 11) > Cells(x, 10) Or _
           WorksheetFunction.CountIf(Cells(x, 33).Resize(, 8), "Y") > 0 Then
        Cells(x, FinalCol + 1) = "ProbSubCon"
    End If

 

[jswhip]

Very good replies all around.

I had also used "On Error Resume Next", but didn't want to inadvertently miss an error.

Thanks again.