Hello all,
I am trying to build a code to calculate the average of all cells above.
How ever the range of above cells vary. Therefore, I declare a variable "Count" to count the total number of cells above to be included in calculation.
As you can see, the macro now calculate the average of range B5:B14 and shows that in B15.
I want to replace the code
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(R[-10]C:R[-1]C),"" "")"
to
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(R[-"Count"]C:R[-1]C),"" "")"
But the syntax is not working after the change.
I also tried the following code
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(R[-Count]C:R[-1]C),"" "")"
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(R[-""Count""]C:R[-1]C),"" "")"
Which shows a the same error message.
Please find attached file with my code.
Hope I explain my problem clearly.
Thank you all in advances for your help.
forum.ozgrid.com/index.php?attachment/42577/