VBA多工作表统计行数时出现溢出错误求助

问题根源 #

1. 变量初始化错误：代码中total = o是字母o而非数字0，会导致变量初始值异常，触发溢出或类型不匹配错误
2. 未限定工作表范围：Rows.Count默认引用活动工作表，遍历第二个工作表时，若活动表不是当前ws，会导致行数计算错误，引发溢出

修改后的完整代码 #

Sub StockAnalysis()
    Dim total As Double
    Dim rowIndex As Long
    Dim change As Double
    Dim columnIndex As Integer
    Dim start As Long
    Dim rowCount As Long
    Dim percentChange As Double
    Dim days As Integer
    Dim dailyChange As Single
    Dim averageChange As Double
    Dim ws As Worksheet

    For Each ws In Worksheets
        columnIndex = 0
        total = 0 ' 修正为数字0，而非字母o
        change = 0
        start = 2
        dailyChange = 0
        
        ' 写入表头
        ws.Range("I1:L1").Value = Array("Ticker", "Yearly Change", "Percent Change", "Total Stock Volume")
        ws.Range("P1:Q1").Value = Array("Ticker", "Value")
        ws.Range("O2:O4").Value = Array("Greatest % Increase", "Greatest % Decrease", "Greatest Total Volume")
        
        ' 限定Rows.Count为当前工作表，避免跨表引用错误
        rowCount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        MsgBox rowCount ' 去掉多余括号，写法更规范
    Next ws
End Sub

关键修改说明 #

• 修正变量初始化：将total = o改为total = 0，确保变量初始值正确
• 限定工作表范围：把Rows.Count改为ws.Rows.Count，保证行数计算基于当前遍历的工作表，避免活动表切换导致的错误
• 优化表头写入：用Array批量赋值表头，代码更简洁高效
• 规范MsgBox写法：去掉不必要的括号，符合VBA语法规范

内容的提问来源于stack exchange，提问作者Kevin Burke