CODEWITHSUNDEEP
vbaexcelwhile-loopoverflow
Bibi
Bibi

Reputation: 11

Overflow fault 6 VBA While-loop

First of all thanks for helping me. I programmed a Sub and I always get an Overflow fault 6. Its in the line where it says While x<160. I cannot do more then 160. E.g. If I do 170 Ill get the fault. But I would like to do While x<1000

The goal is to sum up duration times. Eg. On 1/2/2017 we had 2 downtime for electrical reason, first was 2 minutes, second was 10 minutes.

This macro is making a summary for the 1/2/17 saying that we had 12min downtime because electrical reasons.

I don't do that in a professional way, but this would make my work so much easier! Its working. I only get this annoying Overflow fault if I want to write in large numbers.

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

Upvotes: 1

Views: 297

Answers (1)

user5326167
user5326167

Reputation:

Change the declarations as follows:

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

Do not declare these variables as Integer

Upvotes: 2

Related Questions