overflow error in vba that cannot be fixed by changing variable to long

Question

I am new to coding. the script will correctly output my first variable and then give overflow error.

   Sub hw_vba()

   For Each ws In Worksheets
   Dim WorksheetName As String
   Dim r As Long        'r is a variable to loop through the rows
   Dim volume As Long   'volume is a variable that will hold the total stock 
   volume
   Dim out As Long     'out is a variable to hold the output

    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row   ' Determines the Last Row in ticker column
    Cells(1, 9).Value = "Ticker"
    Cells(1, 10).Value = "Total Volume"
    out = 2

   For r = 2 To LastRow   'should loop thru ticker column from first integer to the last row

    If Cells(r + 1, 1).Value <> Cells(r, 1).Value Then   'will determine when the variable in column 1 changes
    volume = volume + Cells(r, 7).Value          'adds the last value of the first item to volume
    Cells(out, 9) = Cells(r, 1).Value       'outputs the ticker name
    Cells(out, 10).Value = volume             'outputs total volume

                volume = 0                   'resets volume to 0 for next ticker
                out = out + 1                  'increases the row for the output
        Else
            volume = volume + Cells(r, 7).Value             'should add all those lines that are the same

        End If
    Next r
Next ws

End Sub

Variatus · Accepted Answer · 2019-01-21 02:23:22Z

The procedure below is exactly your own code, properly formatted, and deprived of the fatal error induced by placing Dim statements into a loop. You can't Dim a variable more than once in a procedure.

Whether or not the code will actually run or, even better, really do what you want is another matter. You may try. My aim is to provide code here which can be tried and corrected. You try. Let us know where and how it fails (which line, which error?) and you will get help here.

Sub hw_vba()

    ' all of teh Dim statements following should be executed only
    ' once, at the beginning of the code.
    ' therefore they can't be inside a loop which repeats them many times.
'   For Each ws In Worksheets
    Dim WorksheetName As String
    Dim r As Long            'r is a variable to loop through the rows
    Dim volume As Long       'volume is a variable that will hold the total stock
    Dim out As Long          'out is a variable to hold the output        

    For Each ws In Worksheets
        lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row   ' Determines the Last Row in ticker column
        Cells(1, 9).Value = "Ticker"
        Cells(1, 10).Value = "Total Volume"
        out = 2

        For r = 2 To lastrow   'should loop thru ticker column from first integer to the last row
            If Cells(r + 1, 1).Value <> Cells(r, 1).Value Then   'will determine when the variable in column 1 changes
                volume = volume + Cells(r, 7).Value         'adds the last value of the first item to volume
                Cells(out, 9) = Cells(r, 1).Value           'outputs the ticker name
                Cells(out, 10).Value = volume               'outputs total volume
                volume = 0                                  'resets volume to 0 for next ticker
                out = out + 1                               'increases the row for the output
            Else
                volume = volume + Cells(r, 7).Value         'should add all those lines that are the same
            End If
        Next r
    Next ws
End Sub

Collectives™ on Stack Overflow

overflow error in vba that cannot be fixed by changing variable to long

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related