vba__insert to cells =average() with variable range

  • I have a Big table of data which adds a new row for each record (per Day).
    At the bottom for some columns I want to add the formula for 7-day average and 28-day average to some columns.

    the following code does what I want but does not adapt to the addition of new Rows of data

    I have researched and experimented with various alternatives to define the ranges to be averaged

    but am unable to work out the proper syntax to define the ranges to be averaged so that they are not absolute addresses.


  • Re: vba__insert to cells =average() with variable range

    Quote from skywriter;799868

    I would suggest you upload an actual workbook.

    I have uploaded/attached a cut down version of the table

    The whole table is actually created by macro from supporting sheets (not included)

    my macro "Experimental_Report_Summary" prepares what I am aiming for; but the formulae at the bottom of the report are absolute references and do not alter as I add new rows. I want to write vba which will insert this summary with formulae that are relative to the bottom row of data.

    my macro "Experimental_Report_Summary_2" shows some of my failed attempts.

    any help much appreciated


  • Re: vba__insert to cells =average() with variable range

    I'm not 100% sure what you are after, but perhaps this is a step in the right direction. :cheers:

    Sub Formula()
    Dim r As Range
        With Worksheets("TOC").Range("M1").CurrentRegion
                Set r = .Columns("M").Offset(1, 0).Resize(.Rows.Count - 1, 1)
                r.Cells(r.Rows.Count + 2).Formula = "=Average(" & r.Address & ")"
        End With
    End Sub
  • Re: vba__insert to cells =average() with variable range

    Hi skywriter

    Thank you for your code, I have tried it out and seen what it does. It is not quite what I needed;
    however I continued to search and found another answer:-

    ActiveCell.Offset(0, 1).FormulaR1C1 = "=AVERAGE(R[-3]C[0]:R[-09]C[0])"

    this inserts a formula into the cell one column to the right of the ActiveCell
    The formula then skips the two rows above (and in the same column),
    and then selects the range of 7 rows upwards.

    to get VBA to write the full Report Summary:-

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!