溢出故障 6 VBA 循环

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

不要将这些变量声明为整数