Entering a formula in VBA where the range will change

0

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

Answer
Discuss

Answers

0
Selected Answer

First of all, when using VBA don't select anything. Select was created for the purpose of the user's interaction with the worksheet. When VBA takes control it isn't needed because VBA can "see" all cells of all worksheets in the entire workbook at once. Simplified, you can tell VBA to assign the value of "AAA" to cell No. 12345678910, except that you would encode that number for Excel in a way you can understand it, for example, 'Sheet3.Cells(2, 14). Therefore, in VBA the task would be accomplished with one line, 'Sheet3.Cells(2, 14)..Value = "AAA". Therefore,

    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)"

.... translates to


    Dim Bottom As Range
    Dim LastRow As Long
    
    Set Bottom = Cells(Rows.Count, "A").End(xlUp).Offset(1, 4)
    LastRow = Bottom.Row - 1
    Bottom.Formula = "=AVERAGE('Cleansed Data'!o2:'Cleansed Data'!R[lastrow]C15)"

Of course, "=AVERAGE('Cleansed Data'!o2:'Cleansed Data'!R[lastrow]C15)" isn't a valid formula. For one, you can't mix A1 cell addressing with R1C1 addressing in the same formula. For another, 'Cleansed Data'!O2:'Cleansed Data'!R2 is wrong syntax. It should be 'Cleansed Data'!O2:R2. Finally, I wonder why LastRow taken from the ActiveSheet should also be the last row of the Cleansed Data tab.

I think the code you are aiming for is indeed like 'Cleansed Data'!O2:R2, with both start and end columns fixed, not relative to anything that goes on on the current sheet. But I also think that you wish to take the average from different rows. I can't tell which row you have in mind.

However, on the presumption that you can find a way to define the row number I suggest the following VBA code to set the formula.

    Dim R As Long            ' the variable row number
    R = 10          ' Find the value of dynamic R
    Bottom.Formula = "=AVERAGE('Cleansed Data'!$O" & R & ":$R" & R & ")"

With that said, I would consider not using a formal at all. If the averaged data down't change after the line is recorded it would probably be better to let VBA calculate the result and enter it in the cell in place of the formula.

Discuss
1

Variatus, many thanks for your quick response.  My issue was around the formula itself, when the numnber of rows would change from a couple hundred to several thousand depending on which criteria were used, and although I could fund the row number using the code I had written, I hadn't found a way to incorporate this into the formula.

Fortunately a colleague has found a solution which works well and may be of interest to others facing a similar problem.  This is to calculate the Average and Standard Deviation in another tab, using the formula in the form Average(O:O) so it calculates the average of all the values in the column, regardless of the number of rows.  This is working well.

But, thanks again - good to know there are people there who are happy to help.

Discuss

Discussion

Stevey, great to know that you solved your problem. Thank you for posting here how you did it.
Variatus (rep: 2454) Jan 15, '19 at 7:42 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login