CODEWITHSUNDEEP
vbaexceloverflow
Adeeb
Adeeb

Reputation: 1

VBA - runtime error 6. Overflow

This is a code that I am trying to fix. This is from an excel spreadsheet macro.

Ex. When I run the Tally Inspector macro and type in the name of the inspector, the macro is suppose to take in the data form each days of the week and then populate the table which I made. But when I run it, I get the runtime error 6. Overflow error.

The code: 

Sub Tally Inspector()    
    Dim personName As String
    personName = InputBox("Enter Name Of Inspector")

    Dim counter As Integer

    Dim endOfTable
    endOfTable = 130

    Dim witnessSum As Integer: witnessSum = 0 'number witness by CCI
    Dim ICbyCrew As Integer: ICbyCrew = 0 'number done by crew

    Dim columnLetter As Integer: columnLetter = 11 'K

    For counter = 5 To endOfTable
        If StrComp(Cells(counter, columnLetter), personName) = 0 And _
            IsNumeric(Cells(counter, columnLetter - 1)) = True Then

            witnessSum = (witnessSum + Cells(counter, columnLetter - 1).Value)
            ICbyCrew = (ICbyCrew + Cells(counter, 4).Value)
        End If
    Next counter    

    For counter = 150 To 160 Step 1
        If Cells(counter, "E").Value = personName Then
            Cells(counter, "F").Value = ICbyCrew
            Cells(counter, "G").Value = witnessSum
            Cells(counter, "H").Value = (witnessSum / ICbyCrew)* 100 'This line is highlighted when I run the debugger'

            Exit For
        End If
    Next counter
End Sub    

Sub Inspector()

    Dim Inspector As String
    Dim Inspection As Integer: Inspection = 0
    Dim Witness As Integer: Witness = 0

    For x = 155 To 158      
        Inspector = Cells(x, "E")
        If Inspector = "" Then
            Exit For
        End If

        For y = 5 To 120
            If (StrComp(Inspector, Cells(y, "K")) = 0) And (IsNumeric(Cells(y, "D")) = True) And (IsNumeric(Cells(y, "J")) = True) Then
                Inspection = Inspection + Cells(y, "D")
                Witness = Witness + Cells(y, "J")
            End If                
        Next y

    Cells(x, "F") = Inspection
    Cells(x, "G") = Witness
    Cells(x, "H") = (Witness / Inspection) * 100

    Inspection = 0
    Witness = 0        
    Next x    
End Sub

Upvotes: 0

Views: 2918

Answers (2)

Vityata
Vityata

Reputation: 43575

Change your highlighted code from:

Cells(counter, "H").Value = (witnessSum / ICbyCrew) 100

to: 

Cells(counter, "H").Value = (witnessSum / ICbyCrew) / 100

or to:

Cells(counter, "H") = witnessSum / ICbyCrew & 100

or to:

Cells(counter, "H") = witnessSum / ICbyCrew & " " &100

Upvotes: 0

dwirony
dwirony

Reputation: 5450

Redefine your Integer as Long. You're probably running over the 32,767 number limit of Integer.

Upvotes: 1

Related Questions