Sum, Average or Count a higlighted range

[Akroyd]

I have a sheet with numbers everywhere on it, is it possible for a macro to be in place so that when a user highlights a range (could be anything) excel will sum, count and average this range and put the answer in (A1, A2, A3).

Similar to what the toolbar at the bottom

[dimrod]

This probably needs a bit more sophistication, but just off the cuff try this :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[A1].Value = Application.Sum(Selection)
[A2].Value = Application.Count(Selection)
[A3].Value = Application.Average(Selection)
End Sub


[ This Message was edited by: dimrod on 2002-05-23 05:17 ]

[PDuPre']

That is so cool.
Absolutely amazing.

[Ivan Moala]

On 2002-05-23 06:19, PDuPre' wrote:
That is so cool.
Absolutely amazing.

I agree. It's the work of pure genius.

[dimrod]

Thanks Ivan !

[PDuPre']

Modified code to get rid of Divide by Zero error

[A1].Value = Application.Sum(Selection)
[A2].Value = Application.Count(Selection)
[A3].Value = IIf(Application.Count(Selection) = 0, 0, Application.Average(Selection))
--------------------------
Is there a way to toggle this on and off?

[Ivan F Moala]

On 2002-05-23 06:28, Ivan Moala wrote:

I agree. It's the work of pure genius.

Hey...who the hell are you as Bart would say!
The code is good BUT I never made those comments....

Oh well ........
_________________
Kind Regards,
Ivan F Moala From the City of Sails


[ This Message was edited by: Ivan F Moala on 2002-05-23 07:32 ]

[dimrod]

On 2002-05-23 06:47, PDuPre' wrote:
Modified code to get rid of Divide by Zero error

[A1].Value = Application.Sum(Selection)
[A2].Value = Application.Count(Selection)
[A3].Value = IIf(Application.Count(Selection) = 0, 0, Application.Average(Selection))
--------------------------
Is there a way to toggle this on and off?

Does this mean that I am not a genius ?

I don't think the last line of code you posted is going to work.

However, there are various ways you could toggle on/off.

One way would be to have a macro that toggles between EnableEvents=True/EnableEvents=False. But this is not so good because it would affect all event procedures.

Another way would be to have a hidden worksheet(let's say named "Hidden") and a macro(via a button, if you like) available to the relevant worksheet:-

Sub ToggleIt()
With Sheets("Hidden")
If .[A1].Value = "Off" Then
.[A1].Value = "On"
Else
.[A1].Value = "Off"
End If
End With
End Sub

Then the event procedure(in the relevant sheet) would be :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Hidden").[A1].Value = "On" Then
[A1].Value = Application.Sum(Selection)
[A2].Value = Application.Count(Selection)
If Application.Count(Selection) = 0 Then
[A3].Value = 0
Else
[A3].Value = Application.Average(Selection)
End If
End If
End Sub

I haven't tested the above, but anyway there are probably better ways that some of the aspiring genii on this board will no doubt advocate.

[Mr Excel]

On 2002-05-23 07:30, Ivan F Moala wrote:

Hey...who the hell are you as Bart would say!
The code is good BUT I never made those comments....

Oh well ........
_________________
Kind Regards,
Ivan F Moala From the City of Sails


[ This Message was edited by: Ivan F Moala on 2002-05-23 07:32 ]

Hey, Ivan !

Just someone's little joke !

[Ivan F Moala]

On 2002-05-23 07:41, Mr Excel wrote:

Hey, Ivan !

Just someone's little joke !

Yeah I gathered that...have to have a laugh
, I should sign in as Bill gates