使用（r，c）格式来计算具有非连续单元格的平均公式 [英] use (r, c) format for average formula with non-contiguous cells

问题描述

这是我的代码：

Sub sumavg()

With Worksheets(datasummary)
Dim i As Long
Dim j As Long
j = 20

    For i = 1 To 30
        For j = 20 To 25
        Cells(5 * i - 2, j).Value = Average(A(5 * i - 2), G(5 * i - 2), M(5 * i - 2))
        Next j
    Next i

End With
End Sub

我正在努力学习AVERAGE公式的语法。这是我第一次尝试在宏中使用公式。如果可能的话，我想为单元格地址使用（r，c）格式，但到目前为止还没有找到类似的东西。我想在第3行的每个第6个单元格中取平均值并将其打印到T3。这个过程需要循环6次，每个块和40个块。
此时此代码返回一个sub或function not defined错误，并突出显示平均公式中的列标识符。

I am struggling with the syntax for the AVERAGE formula. This is my first attempt at using formulae in macro. If possible, I would like to use the (r, c) format for the cell addresses, but haven't been able to find anything like that so far. I want to average the values in every 6th cell in row 3 and print it to T3. This process needs to be looped through 6 times for each block, and for 40 blocks. At the moment this code is returning a "sub or function not defined" error, and highlighting the column indentifiers in the average formula.

推荐答案

Sub sumavg()
    Dim i As Long, j As Long
    With Worksheets("datasummary")
        For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row Step 5
            For j = 1 To 5
                .Rows(i + 2).Range("T1:Y2").Value = "=IFERROR(AVERAGE(RC[-19],RC[-13],RC[-7]),""N/A"")"
            Next
        Next

    End With
End Sub

这篇关于使用（r，c）格式来计算具有非连续单元格的平均公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！