时间戳

首页 > 解决方案 > 修复使用整数值时的溢出错误

excel - 修复使用整数值时的溢出错误

问题描述

VBA 的新手在这里。这是我下面的代码。我收到运行时错误 6：溢出。我不确定如何进行。我尝试将类型更改为 double 和 long，但错误仍然存​​在。如果我能完成溢出错误，那将会有所帮助。

我的数据非常庞大，我认为错误是由于根据条件添加了每一行值。这段代码的任何缩短也会对我有很大帮助。

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

标签： excelvba

解决方案

如果您切换到 2D 数组m和c

已编译但未测试：

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

推荐阅读