VBA: Find the average of a column with a dynamic range

[suremac]

Greetings,

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.

Thanks for the help in advance,

 

[kumarapush123]

You can insert formula dynamically from Macro itself or even use worksheet function inside a macro as explained in this blog <Blog Link>

I have mentioned that you have a macro, please share us the code that you used to insert the formula, so that someone could work on a solution.

 

[suremac]

Hi,

The part of the code that needs to be altered is:

Sub AveragePercentChange()
'Average Percent Change from 97-2012.
Cells(31, 16) = (Cells(14, 16) + Cells(15, 16) + Cells(16, 16) + Cells(17, 16) + Cells(18, 16) + Cells(19, 16) + Cells(20, 16) + Cells(21, 16) + Cells(22, 16) + Cells(23, 16) + Cells(24, 16) + Cells(25, 16) + Cells(26, 16) + Cells(27, 16) + Cells(28, 16) + Cells(29, 16)) / 16


End Sub

Hopefully there is some code that will enable me to not have to update the formula every time a new data point is added and the range of the column changes.

 

[kumarapush123]

Use this code.

Sub AveragePercentChange()
iRow = 14
Cell_val = Cells(iRow, 3)


While Cell_val <> ""
    iRow = iRow + 1
    Cell_val = Cells(iRow, 3)
Wend


Cells(iRow + 1, 3) = "=AVERAGE(C14:C" & iRow - 1 & ")"


End Sub

 

[suremac]

Thanks @kumarapush123! Your method worked perfectly!