VBA：查找具有动态范围的列的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21434515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: Find the average of a column with a dynamic range
提问by user3242245
I have data in the range P14:P28. I wrote a macro that appends a new data point at P14 every time the macro is used, which increases the range of the column to P14:P29, and also calculates the average of the new range (P14:P29) in cell P31. The problem is that I need the formula to be dynamic, so that when another data point is appended to the table, the average of the newest range (P14:P30) is calculated in cell P32. When the macro is used again, another data point is added, the average is calculated over the range P14:P31 in cell P33, and so on and so forth.
我有 P14:P28 范围内的数据。我编写了一个宏，每次使用该宏时都会在 P14 处附加一个新数据点，从而将列的范围增加到 P14:P29，并计算单元格 P31 中新范围 (P14:P29) 的平均值。问题是我需要公式是动态的，以便当另一个数据点附加到表格时，最新范围 (P14:P30) 的平均值在单元格 P32 中计算。再次使用宏时，会添加另一个数据点，在单元格 P33 中的 P14:P31 范围内计算平均值，依此类推。
Thanks for the help in advance,
我在这里先向您的帮助表示感谢，
采纳答案by user3242245
Here is the code that worked for me:
这是对我有用的代码：
Sub AveragePercentChangeAllSheets()
'Finds the average of a column P with a dynamic range for all the worksheets
Dim i As Long
For i = 1 To Worksheets.Count
Sheets(i).Select
iRow = 14
Cell_val = Cells(iRow, 16)
While Cell_val <> ""
iRow = iRow + 1
Cell_val = Cells(iRow, 16)
Wend
Cells(iRow + 1, 16) = "=AVERAGE(P14:P" & iRow - 1 & ")"
Next i
End Sub
