时间戳

首页 > 解决方案 > vba 中的溢出错误无法通过将变量更改为 long 来修复

excel - vba 中的溢出错误无法通过将变量更改为 long 来修复

问题描述

我是编码新手。该脚本将正确输出我的第一个变量，然后给出溢出错误。 

   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

标签： excelvba

解决方案

下面的过程完全是您自己的代码，格式正确，并且没有因将 Dim 语句放入循环而导致的致命错误。您不能在一个过程中多次对变量进行暗淡处理。

代码是否会真正运行，或者更好的是，是否真的按照您的意愿运行是另一回事。你可以试试。我的目标是在这里提供可以尝试和纠正的代码。你试试。让我们知道它在哪里以及如何失败（哪一行，哪个错误？），您将在这里获得帮助。

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

推荐阅读