VBA股票分析代码出现Run Time Error 1004，求助修复方案

Run Time Error 1004在这个场景里的核心触发原因是引用无效单元格范围、变量逻辑错误导致范围越界，以及未正确声明变量引发的意外行为。你的代码存在多处逻辑和语法问题，下面逐个修正并解释：

核心错误点及修复 #

• 变量声明位置错误：VBA中变量应在过程开头统一声明，循环内声明会导致重复初始化或作用域混乱，需把所有Dim语句移到Sub开头。
• TotalVolume计算逻辑混乱：原代码用Sum从i-TotalVolume+1到i的范围是错误的——TotalVolume是累加的成交量数值，不是行数，直接把累加值赋值到汇总行即可，无需用Sum函数。
• OpeningPrice获取错误：你当前取的是每个Ticker最后一行的开盘价，正确逻辑应该是每个Ticker第一行的开盘价，需在Ticker变化时记录初始开盘价。
• i循环越界：当i=LastRow时，i+1会超出工作表有效行范围，导致ws.Cells(i+1,1)触发1004错误，循环判断要改成i < LastRow，同时单独处理最后一行的Ticker。
• 最值查找范围错误：SummaryRow是下一个待写入的行号，找最值时范围应到SummaryRow-1，否则会包含空单元格导致Match函数出错。
• 未声明变量i：必须显式声明所有变量，添加Dim i As Long避免隐式类型转换错误。

修复后的完整代码 #

Sub StockAnalysis()
    Dim ws As Worksheet
    Dim Ticker As String
    Dim YearlyChange As Double
    Dim PercentChange As Double
    Dim TotalVolume As Double
    Dim LastRow As Long
    Dim SummaryRow As Long
    Dim OpeningPrice As Double
    Dim ClosingPrice As Double
    Dim i As Long
    Dim MaxPercentIncrease As Double
    Dim MaxPercentTicker As String
    Dim MaxPercentDecrease As Double
    Dim MaxPercentDecreaseTicker As String
    Dim MaxTotalVolume As Double
    Dim MaxTotalVolumeTicker As String
    
    For Each ws In ThisWorkbook.Worksheets
        ' 设置汇总表头
        ws.Cells(1, 9).Value = "Ticker"
        ws.Cells(1, 10).Value = "Yearly Change"
        ws.Cells(1, 11).Value = "Percent Change"
        ws.Cells(1, 12).Value = "Total Stock Volume"
        
        ' 设置最值统计表头
        ws.Cells(1, 16).Value = "Ticker"
        ws.Cells(1, 17).Value = "Value"
        ws.Cells(2, 15).Value = "Greatest % Increase"
        ws.Cells(3, 15).Value = "Greatest % Decrease"
        ws.Cells(4, 15).Value = "Greatest Total Volume"
        
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        SummaryRow = 2
        TotalVolume = 0
        ' 初始化第一个Ticker的开盘价
        OpeningPrice = ws.Cells(2, 3).Value
        
        For i = 2 To LastRow
            ' 累加当前行的成交量
            TotalVolume = TotalVolume + ws.Cells(i, 7).Value
            
            ' 判断是否是当前Ticker的最后一行（或循环到最后一行）
            If i = LastRow Or ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
                Ticker = ws.Cells(i, 1).Value
                ClosingPrice = ws.Cells(i, 6).Value
                YearlyChange = ClosingPrice - OpeningPrice
                
                ' 处理开盘价为0的情况，避免除以0错误
                If OpeningPrice <> 0 Then
                    PercentChange = (YearlyChange / OpeningPrice) * 100
                Else
                    PercentChange = 0
                End If
                
                ' 写入汇总数据
                ws.Cells(SummaryRow, 9).Value = Ticker
                ws.Cells(SummaryRow, 10).Value = YearlyChange
                ws.Cells(SummaryRow, 11).Value = PercentChange
                ws.Cells(SummaryRow, 12).Value = TotalVolume
                
                ' 设置格式
                ws.Cells(SummaryRow, 11).NumberFormat = "0.00%"
                ' 根据涨跌设置单元格颜色
                If YearlyChange > 0 Then
                    ws.Cells(SummaryRow, 10).Interior.Color = RGB(0, 255, 0)
                ElseIf YearlyChange < 0 Then
                    ws.Cells(SummaryRow, 10).Interior.Color = RGB(255, 0, 0)
                Else
                    ' 涨跌为0时清除颜色
                    ws.Cells(SummaryRow, 10).Interior.ColorIndex = xlColorIndexNone
                End If
                
                ' 准备下一个Ticker的初始化
                SummaryRow = SummaryRow + 1
                TotalVolume = 0
                ' 如果不是最后一行，获取下一个Ticker的开盘价
                If i <> LastRow Then
                    OpeningPrice = ws.Cells(i + 1, 3).Value
                End If
            End If
        Next i
        
        ' 查找最值（注意范围是到SummaryRow-1，因为SummaryRow是下一个空行）
        If SummaryRow > 2 Then ' 确保有汇总数据才执行
            ' 最大涨幅
            MaxPercentIncrease = Application.WorksheetFunction.Max(ws.Range("K2:K" & SummaryRow - 1))
            MaxPercentTicker = ws.Cells(Application.WorksheetFunction.Match(MaxPercentIncrease, ws.Range("K2:K" & SummaryRow - 1), 0) + 1, 9).Value
            ws.Cells(2, 16).Value = MaxPercentTicker
            ws.Cells(2, 17).Value = MaxPercentIncrease
            ws.Cells(2, 17).NumberFormat = "0.00%"
            
            ' 最大跌幅
            MaxPercentDecrease = Application.WorksheetFunction.Min(ws.Range("K2:K" & SummaryRow - 1))
            MaxPercentDecreaseTicker = ws.Cells(Application.WorksheetFunction.Match(MaxPercentDecrease, ws.Range("K2:K" & SummaryRow - 1), 0) + 1, 9).Value
            ws.Cells(3, 16).Value = MaxPercentDecreaseTicker
            ws.Cells(3, 17).Value = MaxPercentDecrease
            ws.Cells(3, 17).NumberFormat = "0.00%"
            
            ' 最大成交量
            MaxTotalVolume = Application.WorksheetFunction.Max(ws.Range("L2:L" & SummaryRow - 1))
            MaxTotalVolumeTicker = ws.Cells(Application.WorksheetFunction.Match(MaxTotalVolume, ws.Range("L2:L" & SummaryRow - 1), 0) + 1, 9).Value
            ws.Cells(4, 16).Value = MaxTotalVolumeTicker
            ws.Cells(4, 17).Value = MaxTotalVolume
        End If
    Next ws
End Sub

额外优化说明 #

• 增加了涨跌为0时的单元格颜色清除逻辑，避免残留颜色
• 增加SummaryRow > 2的判断，防止工作表无数据时执行最值查找引发错误
• 补充了最值统计的表头，让结果更清晰
• 所有单元格引用都显式关联ws对象，避免默认使用ActiveSheet引发的错误

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