溢出故障6 VBA While循环

Question

首先，谢谢你帮助我。我编写了一个Sub，我总是得到一个溢出错误6.它在代码行中显示为While x<160。超过160我不能做更多。如果我做了170，我就会得到错误。但是我想要do While x<1000

目标是总结持续时间。例如：在2017年1月2日，由于电气原因，我们有2次停机，第一次是2分钟，第二次是10分钟。

这个宏总结了1/2/17，因为电气原因，我们有12分钟的停机时间。

我不是以专业的方式来做这件事，但这会让我的工作变得更容易！它起作用了。只有当我想要写大量数字时，我才会得到这个恼人的溢出错误。

Sub sumuptheduration()


    'Then sum up all the duration times

   Dim a As Integer
   Dim b As Integer
   Dim c As Integer
   Dim x As Long
   Dim summe As Variant
   Dim valuecells As Range


   a = 1
   b = 2
   c = 0
   x = 1

   Set valuecells = Worksheets(1).Range("P2")


   Do While Worksheets(1).Range("C" & b).Value = Worksheets(1).Range("C" & b+a).Value And Worksheets(1).Range("J" & b).Value = Worksheets(1).Range("J" & b + a).Value

        Set valuecells = Worksheets(1).Range("P" & b & ":P" & a + b)
        a = a + 1


    Loop


    Worksheets(1).Range("S" & a + (b - 1)).Value = Application.WorksheetFunction.Sum(valuecells)




    b = a + 2
    c = a + 2
    a = 1




    Dim y As Integer
    y = 0






   While x < 160


    Do While Worksheets(1).Range("C" & b).Value = Worksheets(1).Range("C" & b + a).Value And Worksheets(1).Range("J" & b).Value = Worksheets(1).Range("J" & b + a).Value

        Set valuecells = Worksheets(1).Range("P" & b & ":P" & c + a)

        a = a + 1

    Loop





    Worksheets(1).Range("S" & c + (a - 1)).Value = Application.WorksheetFunction.Sum(valuecells) 'Writes the Sum into the correct Cell (last one of combination)


        'This if clause checks if there are "Single combinations" because single combinations do not go through the do while at the top


        If Worksheets(1).Range("J" & b).Value <> Worksheets(1).Range("J" & b + a).Value And Worksheets(1).Range("J" & b).Value <> Worksheets(1).Range("J" & b + (a - 2)).Value Or Worksheets(1).Range("C" & b).Value <> Worksheets(1).Range("C" & b + a).Value And Worksheets(1).Range("C" & b).Value <> Worksheets(1).Range("C" & b + (a - 2)).Value Then


            Worksheets(1).Range("S" & b).Value = Worksheets(1).Range("P" & b).Value

        Else

        End If


    'Change the variables into correct way for the next passing of the do while clause
    x = x + 1 'causes that do while is not just one time passes
    b = c + a
    c = c + a 'temporary variable to not forget the old b during the next passing of the do while
    a = 1 'at the end of every passing a tells you how much times you needed to pass the do while, so you have to reset it every time



    Wend

      End Sub





here

Answer 1

按如下方式更改声明：

 Dim a As Long
 Dim b As Long
 Dim c As Long

不要将这些变量声明为Integer