Formula in Macros

[S.H.]

I am trying to do a average for a ongoing twelve week
period in my macro. When the macro updates it adds the
last column but I can't get it to drop the first column so
it goes from averaging 12wks then 13wks,14 weeks and so
on. Is there a command I'm missing.
Thanks in advance for any help you can give me.I'm on
Excel 2002.
SH

 

[Bob Phillips]

This code averages the 12 columns in the above row.

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-11]:R[-1]C)"

By its nature, copy this formula across next week and it will pick up just
12 columns.

What does your code look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

This is how mine recorded "
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:RC[-1])"
I use the recorder since I do not know much about visual
basic, just enough to get me kn trouble. Thanks for your
help I will try this right away.
S.H.

-----Original Message-----
This code averages the 12 columns in the above row.

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-11]:R[-1] C)"

By its nature, copy this formula across next week and it will pick up just
12 columns.

What does your code look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am trying to do a average for a ongoing twelve week
period in my macro. When the macro updates it adds the
last column but I can't get it to drop the first column so
it goes from averaging 12wks then 13wks,14 weeks and so
on. Is there a command I'm missing.
Thanks in advance for any help you can give me.I'm on
Excel 2002.
SH

.