Add to Function References

[Rudi]

Hi,

I have a formula in a cell C1 with a range name called MyAVG. (The formula is simply =AVERAGE(A:A))
Each time I run a macro, it inserts two columns to the left of MyAVG and places values in the columns.
Now I want the macro to edit the formula in MyAVG to include the first of those two columns. IOW: The formula is edited to become =AVERAGE(A:A,C:C).
When I run the macro again, it inserts two new columns left of MyAVG, adds values to it and must edit the formula to: =AVERAGE(A:A,C:C,E:E)

Any ideas if this can be done?
TX

[HansV]

Like this:

Code: Select all

    Dim strFormula As String
    strFormula = Range("MyAVG").FormulaR1C1
    strFormula = Left(strFormula, Len(strFormula) - 1) & ",C[-2])"
    Range("MyAVG").FormulaR1C1 = strFormula

[Rudi]

TX...as always.
I tried something similar but just could not resolve the syntax.