Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoke AutoSum From VBA?
I've got a cell selected.
Now I'd like the macro to invoke the AutoSum icon (the little sigma..) that sums the columns above the selected column. But when I try to record a macro, it comes up with the result of the AutoSum (e.g. "=SUM(D3:D8)") instead of a call to AutoSum. Is there a way? The agenda is tb able to create a sum per the rules built into the AutoSum icon and make it bold with a single Ctl+(letter) stroke. I guess the obvious is to start at the selected cell, iterate upwards until a blank cell is found, and then do an =Sum() of what's between. But before I did that, I wanted to see if I was missing something with AutoSum. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoke AutoSum From VBA?
Alt =
Regards, Peter T "PeteCresswell" wrote in message ... I've got a cell selected. Now I'd like the macro to invoke the AutoSum icon (the little sigma..) that sums the columns above the selected column. But when I try to record a macro, it comes up with the result of the AutoSum (e.g. "=SUM(D3:D8)") instead of a call to AutoSum. Is there a way? The agenda is tb able to create a sum per the rules built into the AutoSum icon and make it bold with a single Ctl+(letter) stroke. I guess the obvious is to start at the selected cell, iterate upwards until a blank cell is found, and then do an =Sum() of what's between. But before I did that, I wanted to see if I was missing something with AutoSum. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoke AutoSum From VBA?
With the cells with the values to sum selected:
CommandBars.FindControl(Id:=226).Execute To create the formula: Sub SumAtBottomOfCurrentColumn() Dim myCell As Range Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2) With myCell .Formula = "=SUM(" & _ Range(.Offset(-1, 0), _ Cells(2, .Column)).Address(False, False) & ")" End With End Sub HTH, Bernie MS Excel MVP "PeteCresswell" wrote in message ... I've got a cell selected. Now I'd like the macro to invoke the AutoSum icon (the little sigma..) that sums the columns above the selected column. But when I try to record a macro, it comes up with the result of the AutoSum (e.g. "=SUM(D3:D8)") instead of a call to AutoSum. Is there a way? The agenda is tb able to create a sum per the rules built into the AutoSum icon and make it bold with a single Ctl+(letter) stroke. I guess the obvious is to start at the selected cell, iterate upwards until a blank cell is found, and then do an =Sum() of what's between. But before I did that, I wanted to see if I was missing something with AutoSum. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoke AutoSum From VBA?
Depending on the version of excel you're using, you may see a dropdown arrow on
that autosum icon. This allows you to use other functions, too. Here's a post from Jim Rech posted that takes this into account: This is one way to make Autosum run by code: Sub DoAutoSum() Dim x As CommandBarControl Set x = CommandBars.FindControl(ID:=226) If Val(Application.Version) 9 Then _ Set x = x.Controls(1) x.Execute 'AutoSum If Selection.Cells.Count = 1 Then x.Execute 'Again to exit edit mode End If End Sub Here's another: Sub DoAutoSumWithSendKeys() SendKeys "%={Enter}" End Sub PeteCresswell wrote: I've got a cell selected. Now I'd like the macro to invoke the AutoSum icon (the little sigma..) that sums the columns above the selected column. But when I try to record a macro, it comes up with the result of the AutoSum (e.g. "=SUM(D3:D8)") instead of a call to AutoSum. Is there a way? The agenda is tb able to create a sum per the rules built into the AutoSum icon and make it bold with a single Ctl+(letter) stroke. I guess the obvious is to start at the selected cell, iterate upwards until a blank cell is found, and then do an =Sum() of what's between. But before I did that, I wanted to see if I was missing something with AutoSum. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoke AutoSum From VBA?
Per Dave Peterson:
Sub DoAutoSum() Dim x As CommandBarControl Set x = CommandBars.FindControl(ID:=226) If Val(Application.Version) 9 Then _ Set x = x.Controls(1) x.Execute 'AutoSum If Selection.Cells.Count = 1 Then x.Execute 'Again to exit edit mode End If End Sub That's the one I went with. Works like a champ! Thanks Dave. Thanks Bernie. Thanks Peter. -- PeteCresswell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How 2 sort an autosum total list after adding items 2 autosum item | Excel Discussion (Misc queries) | |||
Invoke hyperlink | Excel Programming | |||
Invoke Java from Excel | Excel Programming | |||
invoke a function | Excel Programming | |||
Invoke a macro with the Enter key | Excel Programming |