如何在Excel VBA中动态设置平均值计算的单元格范围？

问题说明 #

已实现单个单元格复制及固定范围平均值计算，但无法根据匹配行号，动态计算该行第4列至第8列的平均值，相关代码运行报错。

错误代码分析 #

原代码中动态范围的写法不符合VBA语法规范：

rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range(rownumber, 4:rownumber, 8))

VBA中定义单行多列范围，需要使用正确的单元格引用格式，上述写法会导致参数解析失败。

修复方案 #

以下两种写法均可实现动态行范围的平均值计算：

方法1：通过Cells组合范围 #

用Cells(行号, 列号)分别指定范围的起始和结束单元格：

rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range(Worksheets("Master").Cells(rownumber, 4), Worksheets("Master").Cells(rownumber, 8)))

方法2：拼接A1样式范围字符串 #

将行号与列名拼接成标准的A1格式范围字符串：

rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range("D" & rownumber & ":H" & rownumber))

完整修复后代码 #

Sub CalculateDynamicAverage()
    Dim ImportNumRows As Long
    Dim counter As Long
    Dim rng As Range
    Dim rng2 As Range
    Dim rownumber As Long
    
    ' 明确指定工作表，避免默认工作表切换导致的错误
    With Worksheets("Averages")
        ImportNumRows = .Range("A6", .Range("A6").End(xlDown)).Rows.Count
    End With
    
    ' 循环处理每一行数据
    For counter = 6 To 5 + ImportNumRows
        Set rng = Worksheets("Averages").Cells(counter, 1)
        Set rng2 = Worksheets("Master").Columns("A:A").Find(What:=rng.Value, MatchCase:=True)
        
        If Not rng2 Is Nothing Then
            rownumber = rng2.Row
            
            ' 复制匹配行的指定单元格值
            rng.Offset(0, 5) = Worksheets("Master").Cells(rownumber, 2).Value
            ' 固定范围平均值（示例保留）
            rng.Offset(0, 6) = WorksheetFunction.Average(Worksheets("Master").Range("D8:H8"))
            ' 动态行范围平均值（二选一即可）
            rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range(Worksheets("Master").Cells(rownumber, 4), Worksheets("Master").Cells(rownumber, 8)))
            ' rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range("D" & rownumber & ":H" & rownumber))
        End If
    Next counter
End Sub

额外优化提示 #

• 用Long类型替代Integer存储行号，避免行号超过65536时出现溢出错误
• 操作单元格时始终明确指定工作表，防止因当前活动表切换引发的逻辑错误
• 若要避免空值或非数值导致的运行时错误，可将WorksheetFunction.Average替换为Application.Average，后者会返回错误值而非直接中断程序

内容的提问来源于stack exchange，提问作者One foot in the Grave