Fixing overflow error when using Integer value

Question

Novice at VBA here. This is my code below. I am getting a run-time error 6 :overflow. I am not sure how to proceed. I have tried changing the types to double and long, the error still persists. It would of help if I can get the overflow error done.

My data is pretty huge and I assume the error is due to addition of each row value as per the condition. Any shortening of this code would also help me a lot.

Private Sub CommandButton1_Click()
Dim twp(20), carp(20), autop(20), twd(20), card(20), autod(20) As Variant
Dim n, c1(20), c2(20), c3(20), c4(20), c5(20), c6(20), vtype, age, mil, f As 
Integer
Dim m1(20), m2(20), m3(20), m4(20), m5(20), m6(20) As Long

n = ThisWorkbook.Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row

For i = 0 To 19
c1(i) = 0
c2(i) = 0
c3(i) = 0
c4(i) = 0
c5(i) = 0
c6(i) = 0
m1(i) = 0
m2(i) = 0
m3(i) = 0
m4(i) = 0
m5(i) = 0
m6(i) = 0
twp(i) = 0
twd(i) = 0
carp(i) = 0
card(i) = 0
autop(i) = 0
autod(i) = 0
Next i


For i = 3 To n
vtype = ThisWorkbook.Worksheets(1).Cells(i, "E").Value
age = ThisWorkbook.Worksheets(1).Cells(i, "H").Value
mil = ThisWorkbook.Worksheets(1).Cells(i, "O").Value
f = ThisWorkbook.Worksheets(1).Cells(i, "F").Value

If f = 1 Then
If vtype = 1 Then
    If age <= 1 Then
    c1(0) = c1(0) + 1
    m1(0) = m1(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c1(1) = c1(1) + 1
    m1(1) = m1(1) + mil


    ElseIf age <= 3 And age > 2 Then
    c1(2) = c1(2) + 1
    m1(2) = m1(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c1(3) = c1(3) + 1
    m1(3) = m1(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c1(4) = c1(4) + 1
    m1(4) = m1(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c1(5) = c1(5) + 1
    m1(5) = m1(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c1(6) = c1(6) + 1
    m1(6) = m1(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c1(7) = c1(7) + 1
    m1(7) = m1(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c1(8) = c1(8) + 1
    m1(8) = m1(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c1(9) = c1(9) + 1
    m1(9) = m1(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c1(10) = c1(10) + 1
    m1(10) = m1(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c1(11) = c1(11) + 1
    m1(11) = m1(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c1(12) = c1(12) + 1
    m1(12) = m1(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c1(13) = c1(13) + 1
    m1(13) = m1(13) + mil

    ElseIf age <= 15 And age > 13 Then
    c1(14) = c1(14) + 1
    m1(14) = m1(14) + mil

    ElseIf age <= 16 And age > 15 Then
    c1(15) = c1(15) + 1
    m1(15) = m1(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c1(16) = c1(16) + 1
    m1(16) = m1(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c1(17) = c1(17) + 1
    m1(17) = m1(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c1(18) = c1(18) + 1
    m1(18) = m1(18) + mil

    ElseIf age >= 19 Then
    c1(19) = c1(19) + 1
    m1(19) = m1(19) + mil

    End If

  ElseIf vtype = 2 Then
    If age <= 1 Then
    c2(0) = c2(0) + 1
    m2(0) = m2(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c2(1) = c2(1) + 1
    m2(1) = m2(1) + mil

    ElseIf age <= 3 And age > 2 Then
    c2(2) = c2(2) + 1
    m2(2) = m2(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c2(3) = c2(3) + 1
    m2(3) = m2(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c2(4) = c2(4) + 1
    m2(4) = m2(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c2(5) = c2(5) + 1
    m2(5) = m2(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c2(6) = c2(6) + 1
    m2(6) = m2(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c2(7) = c2(7) + 1
    m2(7) = m2(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c2(8) = c2(8) + 1
    m2(8) = m2(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c2(9) = c2(9) + 1
    m2(9) = m2(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c2(10) = c2(10) + 1
    m2(10) = m2(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c2(11) = c2(11) + 1
    m2(11) = m2(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c2(12) = c2(12) + 1
    m2(12) = m2(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c2(13) = c2(13) + 1
    m2(13) = m2(13) + mil

    ElseIf age <= 15 And age > 13 Then
    c2(14) = c2(14) + 1
    m2(14) = m2(14) + mil

    ElseIf age <= 16 And age > 15 Then
    c2(15) = c2(15) + 1
    m2(15) = m2(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c2(16) = c2(16) + 1
    m2(16) = m2(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c2(17) = c2(17) + 1
    m2(17) = m2(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c2(18) = c2(18) + 1
    m2(18) = m2(18) + mil

    ElseIf age >= 19 Then
    c2(19) = c2(19) + 1
    m2(19) = m2(19) + mil

    End If

ElseIf vtype = 3 Then
    If age <= 1 Then
    c3(0) = c3(0) + 1
    m3(0) = m3(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c3(1) = c3(1) + 1
    m3(1) = m3(1) + mil

    ElseIf age <= 3 And age > 2 Then
    c3(2) = c3(2) + 1
    m3(2) = m3(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c3(3) = c3(3) + 1
    m3(3) = m3(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c3(4) = c3(4) + 1
    m3(4) = m3(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c3(5) = c3(5) + 1
    m3(5) = m3(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c3(6) = c3(6) + 1
    m3(6) = m3(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c3(7) = c3(7) + 1
    m3(7) = m3(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c3(8) = c3(8) + 1
    m3(8) = m3(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c3(9) = c3(9) + 1
    m3(9) = m3(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c3(10) = c3(10) + 1
    m3(10) = m3(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c3(11) = c3(11) + 1
    m3(11) = m3(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c3(12) = c3(12) + 1
    m3(12) = m3(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c3(13) = c3(13) + 1
    m3(13) = m3(13) + mil

    ElseIf age <= 15 And age > 14 Then
    c3(14) = c3(14) + 1
    m3(14) = m3(14) + mil


    ElseIf age <= 16 And age > 15 Then
    c3(15) = c3(15) + 1
    m3(15) = m3(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c3(16) = c3(16) + 1
    m3(16) = m3(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c3(17) = c3(17) + 1
    m3(17) = m3(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c3(18) = c3(18) + 1
    m3(18) = m3(18) + mil

    ElseIf age >= 19 Then
    c3(19) = c3(19) + 1
    m3(19) = m3(19) + mil

    End If
    End If

    ElseIf f = 2 Then

    If vtype = 1 Then
    If age <= 1 Then
    c4(0) = c4(0) + 1
    m4(0) = m4(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c4(1) = c4(1) + 1
    m4(1) = m4(1) + mil


    ElseIf age <= 3 And age > 2 Then
    c4(2) = c4(2) + 1
    m4(2) = m4(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c4(3) = c4(3) + 1
    m4(3) = m4(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c4(4) = c4(4) + 1
    m4(4) = m4(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c4(5) = c4(5) + 1
    m4(5) = m4(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c4(6) = c4(6) + 1
    m4(6) = m4(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c4(7) = c4(7) + 1
    m4(7) = m4(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c4(8) = c4(8) + 1
    m4(8) = m4(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c4(9) = c4(9) + 1
    m4(9) = m4(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c4(10) = c4(10) + 1
    m4(10) = m4(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c4(11) = c4(11) + 1
    m4(11) = m4(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c4(12) = c4(12) + 1
    m4(12) = m4(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c4(13) = c4(13) + 1
    m4(13) = m4(13) + mil

    ElseIf age <= 15 And age > 13 Then
    c4(14) = c4(14) + 1
    m4(14) = m4(14) + mil

    ElseIf age <= 16 And age > 15 Then
    c4(15) = c4(15) + 1
    m4(15) = m4(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c4(16) = c4(16) + 1
    m4(16) = m4(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c4(17) = c4(17) + 1
    m4(17) = m4(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c4(18) = c4(18) + 1
    m4(18) = m4(18) + mil

    ElseIf age >= 19 Then
    c4(19) = c4(19) + 1
    m4(19) = m4(19) + mil

    End If

ElseIf vtype = 2 Then
    If age <= 1 Then
    c5(0) = c5(0) + 1
    m5(0) = m5(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c5(1) = c5(1) + 1
    m5(1) = m5(1) + mil

    ElseIf age <= 3 And age > 2 Then
    c5(2) = c5(2) + 1
    m5(2) = m5(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c5(3) = c5(3) + 1
    m5(3) = m5(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c5(4) = c5(4) + 1
    m5(4) = m5(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c5(5) = c5(5) + 1
    m5(5) = m5(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c5(6) = c5(6) + 1
    m5(6) = m5(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c5(7) = c5(7) + 1
    m5(7) = m5(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c5(8) = c5(8) + 1
    m5(8) = m5(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c5(9) = c5(9) + 1
    m5(9) = m5(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c5(10) = c5(10) + 1
    m5(10) = m5(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c5(11) = c5(11) + 1
    m5(11) = m5(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c5(12) = c5(12) + 1
    m5(12) = m5(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c5(13) = c5(13) + 1
    m5(13) = m5(13) + mil

    ElseIf age <= 15 And age > 13 Then
    c5(14) = c5(14) + 1
    m5(14) = m5(14) + mil

    ElseIf age <= 16 And age > 15 Then
    c5(15) = c5(15) + 1
    m5(15) = m5(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c5(16) = c5(16) + 1
    m5(16) = m5(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c5(17) = c5(17) + 1
    m5(17) = m5(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c5(18) = c5(18) + 1
    m5(18) = m5(18) + mil

    ElseIf age >= 19 Then
    c5(19) = c5(19) + 1
    m5(19) = m5(19) + mil

    End If

ElseIf vtype = 3 Then
    If age <= 1 Then
    c6(0) = c6(0) + 1
    m6(0) = m6(0) + mil

    ElseIf age <= 2 And age > 1 Then
    c6(1) = c6(1) + 1
    m6(1) = m6(1) + mil

    ElseIf age <= 3 And age > 2 Then
    c6(2) = c6(2) + 1
    m6(2) = m6(2) + mil

    ElseIf age <= 4 And age > 3 Then
    c6(3) = c6(3) + 1
    m6(3) = m6(3) + mil

    ElseIf age <= 5 And age > 4 Then
    c6(4) = c6(4) + 1
    m6(4) = m6(4) + mil

    ElseIf age <= 6 And age > 5 Then
    c6(5) = c6(5) + 1
    m6(5) = m6(5) + mil

    ElseIf age <= 7 And age > 6 Then
    c6(6) = c6(6) + 1
    m6(6) = m6(6) + mil

    ElseIf age <= 8 And age > 7 Then
    c6(7) = c6(7) + 1
    m6(7) = m6(7) + mil

    ElseIf age <= 9 And age > 8 Then
    c6(8) = c6(8) + 1
    m6(8) = m6(8) + mil

    ElseIf age <= 10 And age > 9 Then
    c6(9) = c6(9) + 1
    m6(9) = m6(9) + mil

    ElseIf age <= 11 And age > 10 Then
    c6(10) = c6(10) + 1
    m6(10) = m6(10) + mil

    ElseIf age <= 12 And age > 11 Then
    c6(11) = c6(11) + 1
    m6(11) = m6(11) + mil

    ElseIf age <= 13 And age > 12 Then
    c6(12) = c6(12) + 1
    m6(12) = m6(12) + mil

    ElseIf age <= 14 And age > 13 Then
    c6(13) = c6(13) + 1
    m6(13) = m6(13) + mil

    ElseIf age <= 15 And age > 14 Then
    c6(14) = c6(14) + 1
    m6(14) = m6(14) + mil


    ElseIf age <= 16 And age > 15 Then
    c6(15) = c6(15) + 1
    m6(15) = m6(15) + mil

    ElseIf age <= 17 And age > 16 Then
    c6(16) = c6(16) + 1
    m6(16) = m6(16) + mil

    ElseIf age <= 18 And age > 17 Then
    c6(17) = c6(17) + 1
    m6(17) = m6(17) + mil

    ElseIf age <= 19 And age > 18 Then
    c6(18) = c6(18) + 1
    m6(18) = m6(18) + mil

    ElseIf age >= 19 Then
    c6(19) = c6(19) + 1
    m6(19) = m6(19) + mil

    End If
End If
End If
Next i

For i = 0 To 19
twd(i) = m1(i) / c1(i)
autod(i) = m2(i) / c2(i)
card(i) = m3(i) / c3(i)

twp(i) = m4(i) / c4(i)
autop(i) = m5(i) / c5(i)
carp(i) = m6(i) / c6(i)

Next i


With ThisWorkbook.Worksheets(2)

.Cells(62, k).Value = twp(0)
.Cells(63, k).Value = twp(1)
.Cells(64, k).Value = twp(2)
.Cells(65, k).Value = twp(3)
.Cells(66, k).Value = twp(4)
.Cells(67, k).Value = twp(5)
.Cells(68, k).Value = twp(6)
.Cells(69, k).Value = twp(7)
.Cells(70, k).Value = twp(10)
.Cells(71, k).Value = twp(11)
.Cells(72, k).Value = twp(12)
.Cells(73, k).Value = twp(13)
.Cells(74, k).Value = twp(14)
.Cells(75, k).Value = twp(15)
.Cells(76, k).Value = twp(16)
.Cells(77, k).Value = twp(17)
.Cells(78, k).Value = twp(18)
.Cells(79, k).Value = twp(19)

.Cells(62, l).Value = autop(0)
.Cells(63, l).Value = autop(1)
.Cells(64, l).Value = autop(2)
.Cells(65, l).Value = autop(3)
.Cells(66, l).Value = autop(4)
.Cells(67, l).Value = autop(5)
.Cells(68, l).Value = autop(6)
.Cells(69, l).Value = autop(7)
.Cells(70, l).Value = autop(10)
.Cells(71, l).Value = autop(11)
.Cells(72, l).Value = autop(12)
.Cells(73, l).Value = autop(13)
.Cells(74, l).Value = autop(14)
.Cells(75, l).Value = autop(15)
.Cells(76, l).Value = autop(16)
.Cells(77, l).Value = autop(17)
.Cells(78, l).Value = autop(18)
.Cells(79, l).Value = autop(19)

.Cells(62, m).Value = carp(0)
.Cells(63, m).Value = carp(1)
.Cells(64, m).Value = carp(2)
.Cells(65, m).Value = carp(3)
.Cells(66, m).Value = carp(4)
.Cells(67, m).Value = carp(5)
.Cells(68, m).Value = carp(6)
.Cells(69, m).Value = carp(7)
.Cells(70, m).Value = carp(10)
.Cells(71, m).Value = carp(11)
.Cells(72, m).Value = carp(12)
.Cells(73, m).Value = carp(13)
.Cells(74, m).Value = carp(14)
.Cells(75, m).Value = carp(15)
.Cells(76, m).Value = carp(16)
.Cells(77, m).Value = carp(17)
.Cells(78, m).Value = carp(18)
.Cells(79, m).Value = carp(19)

End With
End Sub

Dim n, c1(20), c2(20), c3(20), c4(20), c5(20), c6(20), vtype, age, mil, f As Integer only sets f to an integer, all the others are variants. Ditto for your other lines. You could replace your if.. else ifs with Select Case, and you can set all those cells with a for next loop and and .Offset. Once you've refactored your code you will probably spot the mistake - at the moment it's pretty unreadable — Harassed Dad
– Harassed Dad, Commented Dec 11, 2018 at 15:17
Can't help thinking the whole thing would be more easily handled as one or more 2-d arrays instead of multiple 1-d arrays. That would remove the vast bulk of your If-Then logic since you can use the rounded-up age to index into the 2D array instead of switching between different 1D arrays. BTW Dim twp(20) is the same as Dim twp(0 to 20) so your arrays all have an empty slot at the end... — Tim Williams
– Tim Williams, Commented Dec 11, 2018 at 17:47

Tim Williams · Accepted Answer · 2018-12-11 22:32:34Z

Your code will be much shorter if you switch to 2D arrays for m and c

Compiled but not tested:

Private Sub CommandButton1_Click()

    Dim sht As Worksheet

    Dim twp(1 To 20), carp(1 To 20), autop(1 To 20)
    Dim twd(1 To 20), card(1 To 20), autod(1 To 20) As Variant
    Dim n As Long, c(1 To 20, 1 To 6), vtype, age, mil, f As Integer
    Dim m(1 To 20, 1 To 6) As Long, i As Long, x As Long, colIndex As Long

    Set sht = ThisWorkbook.Worksheets(1)

    n = sht.Cells(Rows.Count, "C").End(xlUp).Row

    For i = 1 To 20
        For x = 1 To 6
            c(i, x) = 0
            m(i, x) = 0
        Next x
        twp(i) = 0
        twd(i) = 0
        carp(i) = 0
        card(i) = 0
        autop(i) = 0
        autod(i) = 0
    Next i


    For i = 3 To n

        vtype = sht.Cells(i, "E").Value
        age = sht.Cells(i, "H").Value
        mil = sht.Cells(i, "O").Value
        f = sht.Cells(i, "F").Value

        age = Application.Ceiling(age, 1) '<< round up
        age = Application.Min(age, 20)    '<< cap at 20

        colIndex = vtype + IIf(f = 2, 3, 0)

        If f = 1 Or f = 2 Then
            c(age, colIndex) = c(age, colIndex) + 1
            m(age, colIndex) = m(age, colIndex) + mil
        End If

    Next i

    For i = 1 To 20
        'add some error checking here to prevent
        '  divide-by-zero errors
        twd(i) = m(i, 1) / c(i, 1)
        autod(i) = m(i, 2) / c(i, 2)
        card(i) = m(i, 3) / c(i, 3)
        twp(i) = m(i, 4) / c(i, 4)
        autop(i) = m(i, 5) / c(i, 5)
        carp(i) = m(i, 6) / c(i, 6)
    Next i


    With ThisWorkbook.Worksheets(2)
        For i = 1 To 20
            .Cells(61, "k").Offset(i, 0).Value = twp(i)
            .Cells(61, "l").Offset(i, 0).Value = autop(i)
            .Cells(61, "m").Offset(i, 0).Value = carp(i)
        Next i
    End With

End Sub

user10773628 · Accepted Answer · 2018-12-11 15:24:37Z

0

twd(i) = m1(i) / c1(i)

I cannot find any guarantee or error control to ensure that the cn arrays are non-zero.

You cannot divide a number by zero (i.e. zero cannot be the denominator).

answered Dec 11, 2018 at 15:24

user10773628

Collectives™ on Stack Overflow

Fixing overflow error when using Integer value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related