CODEWITHSUNDEEP
vbaexcel
Josh
Josh

Reputation: 137

Excel VBA protecting calculation from dividing by 0

I am trying to populate cells with data based on a simple division but there are (and will always be) instances where I have a 0 on one or both ends of the equation.

Is it possible to wrap some kind of protection around the equation so that if it's a division by 0 it just sets the value to 0 rather then error out?

My code

Set myRange = Range("S3:S20")
rowCounter = 3
For Each myCell In myRange
    If Range("H1").Value Like "*Mon*" Then
        myCell.Value = Range("Q" & rowCounter).Value
        rowCounter = rowCounter + 1

    Else
        myCell.Value = ((myCell.Value + Range("Q" & rowCounter).Value) / Range("R" & rowCounter).Value)
        rowCounter = rowCounter + 1

    End If
Next myCell

Below is the data referenced in the equation

P    Q      R    S
5   1:03    5   1:03
0   0:00    0   0:00
0   0:00    0   0:00
7   0:19    7   0:19
0   0:00    0   0:00
0   0:00    0   0:00
12  0:26    12  0:26
3   0:15    3   0:15
3   1:16    3   1:16
7   0:29    7   0:29
9   0:14    9   0:14
0   0:00    0   0:00
0   0:00    0   0:00
6   0:28    6   0:28
0   0:00    0   0:00
4   0:15    4   0:15
0   0:00    0   0:00
0   0:00    0   0:00

Thanks for looking!

Upvotes: 1

Views: 830

Answers (3)

user3598756
user3598756

Reputation: 29421

I answer your question along with some other considerations:

  • you can exploit IfError() WorksheetFunction to trap the error

  • it's a logical error to repeat If Range("H1").Value Like "*Mon*" Then at every iteration

    check it once at the beginning and then proceed accordingly

  • you can avoid counter variable by offsetting the loop range variable

for all what above you could code

Sub main()
    Dim myRange As Range, myCell As Range

    Set myRange = Range("S3:S20")
    If Range("H1").Value Like "*Mon*" Then
        myRange.Value = myRange.Offset(, -2).Value
    Else
        For Each myCell In myRange
            myCell.FormulaR1C1 = "=iferror((" & myCell.Value & "+ RC[-2])/RC[-1],0)"
        Next myCell
        myRange.Value = myRange.Value
    End If
End Sub

Upvotes: 5

Shai Rado
Shai Rado

Reputation: 33682

Try the code below (check if the value in Column R = 0).

Since you are looping with For Each myCell In myRange, there is no need to have another variable (rowCounter) to keep the row number, just use myCell.Row.

Option Explicit

Sub HandleDev_ByZero()

Dim myRange As Range, myCell As Range

Set myRange = Range("S3:S20")
For Each myCell In myRange
    If Range("H1").Value Like "*Mon*" Then
        myCell.Value = Range("Q" & myCell.Row).Value
    Else
        If Range("R" & myCell.Row).Value = 0 Then ' check if 0
            myCell.Value = 0
        Else
            myCell.Value = ((myCell.Value + Range("Q" & myCell.Row).Value) / Range("R" & myCell.Row).Value)
        End If
    End If
Next myCell

End Sub

Upvotes: 3

VBA Pete
VBA Pete

Reputation: 2666

How about an extra ElseIF statement that check for the value in column R:

Set myRange = Range("S3:S20")
rowCounter = 3
For Each myCell In myRange
        If Range("H1").Value Like "*Mon*" Then
            myCell.Value = Range("Q" & rowCounter).Value
            rowCounter = rowCounter + 1
        ElseIf Range("R" & rowCounter).Value <> "0" Then
            myCell.Value = ((myCell.Value + Range("Q" & rowCounter).Value) / Range("R" & rowCounter).Value)
            rowCounter = rowCounter + 1
        Else
            myCell.Value = 0
            rowCounter = rowCounter + 1
        End If
Next myCell

Upvotes: 3

Related Questions