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