AutoSum in Excel XP

[Juan Pablo González]

If you need it (Altough I would recommend other ways of doing this) here's the code to use the AutoSum button in Excel XP.

Sub TestAutoSumXP()

    Application.CommandBars.FindControl(ID:=226).Execute

    Application.SendKeys "S{Enter}"

End Sub

The difference between this and previous versions is that in previous versions you needed to do a "double" execute of the CommandBarButton in order to get the result. Now, in XP, since you have other Autofunctions (AutoAverage, Autocount,etc.), the problem is a little different, but not that much.

I really hope that using Sendkeys doesn't create a mess for anyone, but, hopefuly, it won't.

[Mark O'Brien]

Any way to avoid using sendkeys?

[Juan Pablo González]

Looking into it Mark, but, my recommendation would be to use something like this:

Rng.FormulaR1C1 = "=SUM(R[-1]C:R2C)"

which would execute faster, and without using sendkeys... only "problem" for this method is that you "have" to know the top row of the data (R2 in my example).

[Mark O'Brien]

What about using a ControlID? (bed-time for me, I'm trying to be difficult on purpose now)

[Dave Hawley]

Now if you guys only subscribed to my free newsletter you would know all this stuff

Sub SumUp()
Dim strAddress As String

strAddress = _
Range("A1", Range("A65536").End(xlUp)).Address

 If Range(strAddress).Cells.Count > 1 Then
    Range("A65536").End(xlUp).Offset(1, 0).Formula = _
                         "=Sum(" & strAddress & ")"
 End If
End Sub


Sub SumDown()
Dim strAddress As String

strAddress =Range("A2", Range("A65536").End(xlUp)).Address

 If Range(strAddress).Cells.Count > 1 Then
     Range("A1").Formula ="=Sum(" & strAddress & ")"
 End If
End Sub


Sub SumRight()
Dim strAddress As String

strAddress =Range("B1", Range("IV1").End(xlToLeft)).Address

 If Range(strAddress).Cells.Count > 1 Then
    Range("A1").Formula = "=Sum(" & strAddress & ")"
 End If
End Sub


Sub SumLeft()
Dim strAddress As String

strAddress =Range("A1", Range("IV1").End(xlToLeft)).Address
 If Range(strAddress).Cells.Count > 1 Then
      Range("IV1").End(xlToLeft).Offset(0, 1).Formula = _
                             "=Sum(" & strAddress & ")"
 End If
End Sub

Taken from Issue 14
http://www.ozgrid.com/News/Archive.htm



_________________
Regards
Dave Hawley
8 Add-ins 1, with free File Size Reducer
40+ more here
OzGrid.com

[ This Message was edited by: Dave Hawley on 2002-07-19 23:56 ]

[Juan Pablo González]

Wow that's REALLY ugly Dave and you want to know something ? you really ought to take a look at your subscribers list... you may find some surprises.

AND GUESS WHAT... HA, FOUND A WAY... it's really cool, simple, same "principle" as in 2000, but with a twist.

Sub TestAutoSumXP()

    Application.CommandBars("Auto Sum").Controls(1).Execute

    Application.CommandBars("Auto Sum").Controls(1).Execute

End Sub

That will work perfectly. Now, here's the funny thing, the Controls(1) CommandBarButton is actually the AutoSum, If I check it like this:

?Application.CommandBars("Auto Sum").Controls(1).Id
226

I get 226, which is the same Id for the AutoSum in the Standard toolbar... BUT, this code WILL NOT work the same as the above... how's that?

Sub TestAutoSumXP2()

    Application.CommandBars.FindControl(ID:=226).Execute

    Application.CommandBars.FindControl(ID:=226).Execute

End Sub

[Mark O'Brien]

[quote]
Now if you guys only subscribed to my free newsletter you would know all this stuff [/quote/

I've hit your site about 20 times today. I'm not giving you another one just to sign up for a newsletter.

Juan,

You took your time coming up with that one. I bet Colo would have been able to tell you that.

(seriously though, good job)

[Juan Pablo González]

On 2002-07-20 00:04, Mark O'Brien wrote:
You took your time coming up with that one. I bet Colo would have been able to tell you that.

Well, if he had XP then probably... but that's not the reality right now, so sorry

[Dave Hawley]

Ugly you say! You have never seen me IRL have you, then you will no what ugly is Me thinks it's quite elegant, you do realise it's 4 seperate procedures don't you?

Sub SumUp()
Dim strAddress As String

strAddress = _
Range("A1", Range("A65536").End(xlUp)).Address

 If Range(strAddress).Cells.Count > 1 Then
    Range("A65536").End(xlUp).Offset(1, 0).Formula = _
                         "=Sum(" & strAddress & ")"
 End If
End Sub

Mine has one up on the AutoSum, in that mine looks past any blank cells.

Juan, does Bill know your on my newsletter list? Or is he on there as well?

Mark, I have been getting all excited lately as the 20 hits per day (from you) means my traffic has DOUBLED! Why did you go and burst my bubble

[njones4213]

Sorry to bump a really really old thread, but this has been driving me insane. I'm trying to do this very similar to what was posted, but it will not work for me. There are several groups on the sheet that I need to sum. I have already subtotaled the sheet so I cannot use that function. I cannot use some of the more creative solutions on here as the start at the bottom and go to the first non blank line.... I have many groups and will have to repeat the procedure for each of them. When I try using the

Application.CommandBars("Auto Sum").Controls(1).Execute

All I receive is the Sum function. No range is selected and therefore will still require user interaction. I know this is a bit vague, but I am at the end of my rope here.

Thanks,
Nick