Does anyone know VBA syntax for Excel SUM button? 1

[MSUser]

I would like to change it to AVERAGE

 

[SkipVought]

Hi,

x = Application.SUM(MyRange)
y = Application.AVERAGE(MyRange)

Hope this helps
Skip,
SkipAndMary1017@mindspring.com

 

[MSUser]

This does not seem to work.

 

[SkipVought]

What is your syntax? - please include all. Skip,
SkipAndMary1017@mindspring.com

 

[MSUser]

ActiveCell.Offset(0, 0).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-12]C:R[-1]C)"
End Sub

 

[SkipVought]

Range("A1&quot.Value = Application.Average(YourRange)

puts the value in the cell and

Range("A1&quot.Formula= "=Average(" & YourRange.Address & &quot"

puts a formula in the cell

Skip,
SkipAndMary1017@mindspring.com

 

[MSUser]

Thanks for your help,but how can I get it work just like the SUM button; where the it seeks the cell (closest numbe value(s)) to average.

 

[Zathras]

I don't think you can. I use the SUM button and then F2 (edit) to change the SUM to AVERAGE. You could write a macro to do that and put it on another button. But what's the point? The SUM button is designed for initial set-up. What is the underlying requirement you are trying to meet?

BTW, if you haven't tried it yet, you can click the sum button twice for the (almost) same effect as clicking it once followed by the enter key. The only difference is the selection stays on the cell with SUM formula and does not auto-advance (assuming auto-advance is active).