CODEWITHSUNDEEP
vbaexcelloopsinteger-overflow
HP-Nunes
HP-Nunes

Reputation: 111

Not able to run an Excel Macro loop throughout the workbook due to overflow error

I have 7 sheets with over 70,000 entries; which I am summing per unique attribute in a separate column on each sheet.

While the macro works fine on a single sheet, I am having difficulty looping my macro through every sheet, and am provided with an overflow error.

I am thinking that either the sheer sum of querying is unfit for Excel, or that I need to somehow redefine my outputs as long integers.

Sub Summation()

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For X = 1 To WS_Count

    Sheets(X).Select

     Dim total As Variant

     Dim attribute As String
     Dim data_last_row As Variant
     Dim attribute_last_row As Variant

     Range("I1").Value = "Unique Attribute"
     Range("L1").Value = "Total"

     data_last_row = Cells(Rows.Count, "A").End(xlUp).Row

     For I = 2 To data_last_row
         attribute = ws.Cells(I, 1)
         total = Cells(I, 7) + total
         attribute_last_row = Cells(Rows.Count, "I").End(xlUp).Row
         If Cells(I + 1, 1) <> attribute Then
             Cells(attribute _last_row + 1, 9) = attribute 
         End If
         Cells(attribute _last_row + 1, 12) = total        
     Next I

Next X

End Sub

I also tried For Each ws In Worksheets and defining my Cells as ws, but the outputs were way off in every subsequent sheet, replicating essentially the results of the macro on the first sheet.

Is there another way, or is Excel simply not suited to process this much data?

Upvotes: 0

Views: 106

Answers (1)

adam
adam

Reputation: 424

Should kind of work now.

attribute should not be declared as a variable as it is a keyword

attributes _last_row had a space in the middle. Probably a typo but do take note. 

Sub Summation()

Dim WS_Count As Integer
Dim I As Long
Dim ws As Worksheet

WS_Count = ActiveWorkbook.Worksheets.Count

For X = 1 To WS_Count

    Set ws = Sheets(X)

    Dim total As Variant


    Dim attributes As String
    Dim data_last_row As Variant
    Dim attributes_last_row As Variant

    ws.Range("I1").Value = "Unique Attribute"
    ws.Range("L1").Value = "Total"

    data_last_row = Cells(ws.Rows.Count, "A").End(xlUp).Row

    For I = 2 To data_last_row
        attributes = ws.Cells(I, 1)
        total = ws.Cells(I, 7) + total
        attributes_last_row = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
        If Cells(I + 1, 1) <> attributes Then
             ws.Cells(attributes_last_row + 1, 9) = attributes
        End If

        ws.Cells(attributes_last_row + 1, 12) = total

    Next I

Next X

End Sub

Upvotes: 1

Related Questions