Runtime error overflow

Question

I am trying to do the following. I am having two sheets.

From sheet1, I count number of 0 according to the condition and copy it in the table in sheet2.

I am doing the same with different conditions. When I am executing the code, I am getting an runtime error, Overflow. Can someone help me what is the reason.

Sub result()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim cnt As Integer
    Dim cntU As Integer
    Dim Sht As Worksheet
    Dim totalrows As Long

    Set Sht = Sheets("CTT")
    Sheets("Sheet1").Select

    totalrows = Range("A5").End(xlDown).Row

    n = Worksheets("Sheet1").Range("A5:A" & totalrows).Cells.SpecialCells(xlCellTypeConstants).Count

    For i = 2 To WorksheetFunction.Count(Sht.Columns(1))
        cntT = 0
        cntU = 0
        Cnts = 0
        cntV = 0
        cntZ = 0
        cntW = 0
        cntA = 0
        Cntb = 0
        cntC = 0
        cntD = 0
        cntE = 0
        cntF = 0

        If Sht.Range("A" & i) = Val(Format(Now, "WW")) Then Exit For
    Next i

    For j = 5 To Sheets("Sheet1").Cells(Rows.Count, 17).End(xlUp).Row
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "D" Then cntT = cntT + 1
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "K" Then cntU = cntU + 1
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "A" Then Cnts = Cnts + 1
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "M" Then cntV = cntV + 1
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "C" Then cntW = cntW + 1
        If Sht.Range("A" & i) = Range("W" & j) And Range("Q" & j) = "E" Then cntZ = cntZ + 1
        If cntU <> 0 Then Sht.Range("K" & i) = cntU
        If Cnts <> 0 Then Sht.Range("B" & i) = Cnts
        If cntT <> 0 Then Sht.Range("E" & i) = cntT
        If n <> 0 Then Sht.Range("T" & i) = n
        If cntV <> 0 Then Sht.Range("N" & i) = cntV
        If cntZ <> 0 Then Sht.Range("H" & i) = cntZ
        If cntZ <> 0 Then Sht.Range("Q" & i) = cntW
    Next j

    For k = 5 To Sheets("CTT_Report").Cells(Rows.Count, 17).End(xlUp).Row
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "A" And Range("U" & k) = "0" Then cntA = cntA + 1
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "D" And Range("U" & k) = "0" Then Cntb = Cntb + 1
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "E" And Range("U" & k) = "0" Then cntC = cntC + 1
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "K" And Range("U" & k) = "0" Then cntD = cntD + 1
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "M" And Range("U" & k) = "0" Then cntE = cntE + 1
        If Sht.Range("A" & i) = Range("W" & k) And Range("Q" & k) = "C" And Range("U" & k) = "0" Then cntF = cntF + 1

        If cntA <> 0 Then Sht.Range("C" & i) = cntA
        If Cntb <> 0 Then Sht.Range("F" & i) = Cntb
        If cntC <> 0 Then Sht.Range("I" & i) = cntC
        If cntD <> 0 Then Sht.Range("L" & i) = cntD
        If cntE <> 0 Then Sht.Range("O" & i) = cntE
        If cntF <> 0 Then Sht.Range("R" & i) = cntF
    Next k

    If cntA + Cnts + Cntb + cntC + cntD + cntE + cntF + cntT + cntU + cntV + cntZ <> 0 Then
        Sht.Range("D" & i) = cntA / Cnts
        Sht.Range("G" & i) = Cntb / cntT
        Sht.Range("J" & i) = cntC / cntZ
        Sht.Range("M" & i) = cntD / cntU
        Sht.Range("P" & i) = cntE / cntV
        Sht.Range("S" & i) = cntF / cntW
    End If

End Sub

@BrandonBarney Sht.Range("S" & i) = cntF / cntW in this line — Jenny
– Jenny, Commented Aug 3, 2017 at 12:15

Brandon Barney · Accepted Answer · 2017-08-03 12:31:15Z

1

I am pretty much willing to bet that you are getting your error in this location:

If cntA + Cnts + Cntb + cntC + cntD + cntE + cntF + cntT + cntU + cntV + cntZ <> 0 Then
    Sht.Range("D" & i) = cntA / Cnts
    Sht.Range("G" & i) = Cntb / cntT
    Sht.Range("J" & i) = cntC / cntZ
    Sht.Range("M" & i) = cntD / cntU
    Sht.Range("P" & i) = cntE / cntV
    Sht.Range("S" & i) = cntF / cntW
End If

While the suggestion posted by Sam is the first place to look (Integers have a max value of ~32,000 whereas Longs have a max value of ~2 billion), the second place to always look is divisions by 0.

While there is an error code for division by 0, you may encounter instances where you have a division by 0 that results in an overflow error. The best way to fix this is something like this:

If cntA + Cnts + Cntb + cntC + cntD + cntE + cntF + cntT + cntU + cntV + cntZ <> 0 Then
        If Cnts <> 0 Then
            Sht.Range("D" & i).value = cntA / Cnts
        Else
            Sht.Range("D" & i).value = 0
        End If

        If cntT <> 0 Then
            Sht.Range("G" & i).value = Cntb / cntT
        Else
            Sht.Range("G" & i).value = 0
        End If

        If cntZ <> 0 Then
            Sht.Range("J" & i).value = cntC / cntZ
        Else
            Sht.Range("J" & i).value = 0
        End If

        If cntU <> 0 Then
            Sht.Range("M" & i).value = cntD / cntU
        Else
            Sht.Range("M" & i).value = 0
        End If

        If cntV <> 0 Then
            Sht.Range("P" & i).value = cntE / cntV
        Else
            Sht.Range("P" & i).value = 0
        End If

        If cntW <> 0 Then
            Sht.Range("S" & i).value = cntF / cntW
        Else
            Sht.Range("S" & i).value = 0
        End If
End If

While this will do the trick, if I was writing the code I would likely try to wrap this in some kind of function. You could even write a function that divides two numbers, and returns 0 if the denominator is 0. I'll leave that up to you though.

Also, I highly recommend refactoring this code. You should check out Rubberduck : http://rubberduckvba.com/. It is a fantastic tool that can get you well on your way to writing better code.

I hope this helps!

edited Aug 3, 2017 at 12:31

answered Aug 3, 2017 at 12:16

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vityata Over a year ago

You have exactly 6 big errors in your code. Can you find them by yourself?

Brandon Barney Over a year ago

I realized as I was typing a reply that I couldnt find them that someone could theoretically use a negative number and still be valid. My mistake :).

Vityata Over a year ago

Exactly. But you can still return NaN, Div/0! or Error in stead of 0.

Vityata · Accepted Answer · 2017-08-03 12:25:04Z

1

Most probably cntW = 0 and thus deletion on the line (Sht.Range("S" & i) = cntF / cntW) is not possible, as far as it is not possible to divide by zero.

Fix your code to make sure it is not happening. Like this: If cntW <> 0 then Sht.Range("S" & i) = cntF / cntW

To check what is the value of cntW, write the following:

MsgBox cntW somewhere before the error.

edited Aug 3, 2017 at 12:25

answered Aug 3, 2017 at 12:18

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Jenny Over a year ago

the value of cntF and cntW are 0,

Vityata Over a year ago

@Jenny - and this is exactly why you get the error. You should think about a logic to write something else in this case - e.g. Division by zero is not allowed

Sam · Accepted Answer · 2017-08-03 11:49:14Z

0

Change all Integer variables to Long and try again

answered Aug 3, 2017 at 11:49

Sam

5,7411 gold badge21 silver badges36 bronze badges

1 Comment

Brandon Barney Over a year ago

@Sam Please be sure to explain why someone should change to Long. For most new users your answer wouldnt make sense without knowing the source of the issue that your solution would solve :).

Collectives™ on Stack Overflow

Runtime error overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Collectives™ on Stack Overflow

Runtime error overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related