0

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
8
  • At what line do you get the error? Commented Aug 1, 2017 at 15:45
  • x and y are completely undefined. Its best practice to use option explicit and define variable types explicitly. Commented Aug 1, 2017 at 15:51
  • "Cells(counter, "H").Value = (witnessSum / ICbyCrew) 100" --Line 34 Commented Aug 1, 2017 at 15:52
  • What is Tally Inspector? Commented Aug 1, 2017 at 15:55
  • 1
    Is the variable ICbyCrew by chance equal to zero? Commented Aug 1, 2017 at 16:10

2 Answers 2

1

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

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

4 Comments

you're probably running over the 32,767 number limit of integer - the code actual sets hard beginning and end points to all define variables (x and y, however are not defined anywhere!)
@ScottHoltzman Hey Scott, read your comment yesterday but wasn't quite sure what you meant - Care to elaborate?
For example, in the line: For counter = 5 To endOfTable user sets endOfTable to a defined value of 130 which integer will handle. Other places this happens as well where counter is used in a loop.
Ahhh I see. To be honest, I hardly even looked at the code... I just saw that there were integers defined here and there, and with the overflow error I assumed that one of them needed to be a long. Thanks for clearing that up-
0

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

3 Comments

Cells(counter, "H").Value = (witnessSum / ICbyCrew) * 100
@Adeeb - what exactly should be staying in this cell?
Its just a column in my spreadsheet which calculates the percentage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.