I have a macro which copies data into an analysis sheet depending on certain criteria. This means that the number of rows may change, so the two formulae that I am adding at the bottom of the table need to change. At the moment I get to the correct first cell (first data column, row immediately underneath the data table) using Offset commands. I am then trying to enter an AVERAGE calculation and a STDEV caluclation in the two rows below, always starting at cell O2, but ending at the last row above the formula. I have tried various methods, but the current version tries to use the Range fuinction. Most of my attempts end up that the formula entered by the Marco is exactly as typed, i.e. as text, not as a formula with cell references. The current code (which just fails without entering anything) is like this:

CODE

Range("A1").End(xlDown).Select

ActiveCell.Offset(0, 14).Select

Dim bottom As Range

Set bottom = ActiveCell

lastrow = ActiveCell.Row

ActiveCell.Offset(1, 0).Select

ActiveCell.Formula = "=AVERAGE('Cleansed Data'!o2:'Cleansed Data'!R[lastrow]C15)"

bottom.Offset(2, 0).Select

ActiveCell.Formula = "=STDEV('Cleansed Data'!o2:'Cleansed Data'!R[lastrow]C15)"

CODE

Another attempt for the formula line which appears to enter the formula correctly using the relative referencing, but returns a #NAME? error is as below.

Any help much appreciated.

CODE

ActiveCell.Formula = "=AVERAGE('Cleansed Data'!o2:'Cleansed Data'!R[-1]C15)"

CODE