Prev: Vlookup with more than 15 digits
Next: Count Blank Cells & Letter P
From: BRC on 11 Jan 2010 15:47
Can anyone tell me if there is vba command to auto sum a range similar
to pushing the autosum button ∑ in excel 2007? In this situation the
cells may not always be in the same location so hard coding not
practical. I can select the range, but can't find command to autosum.
I found the the snippet below but it generates error in excel 2007.
Thanks for any advice. BRC
CommandBars.FindControl(ID:=226).Execute 'AutoSum
From: Gord Dibben on 11 Jan 2010 16:32
Where would you like the results to appear?

Below leftmost column of selected range.

Sub Sum_Range()
Set rng = Selection
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
End Sub

Below or top right depending upon number of columns.

Sub Sum_Range()
Set rng = Selection
If rng.Columns.Count > 1 Then
Set rng1 = rng.Offset(0, rng.Columns.Count).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
Else
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 11 Jan 2010 12:47:16 -0800 (PST), BRC <brc1051-googrps(a)yahoo.com>
wrote:

>Can anyone tell me if there is vba command to auto sum a range similar
>to pushing the autosum button ? in excel 2007? In this situation the
>cells may not always be in the same location so hard coding not
>practical. I can select the range, but can't find command to autosum.
>I found the the snippet below but it generates error in excel 2007.
>Thanks for any advice. BRC
>CommandBars.FindControl(ID:=226).Execute 'AutoSum

 | 
Pages: 1
Prev: Vlookup with more than 15 digits
Next: Count Blank Cells & Letter P