Excel Experts,

I wrote the below code which seems to be effective in putting an autosum
formula below a column of numbers such as this one. I select the cell below
the numbers and run the macro.

A B
1
2 100
3 200
4 50
5

However, how can I modify it so if I have a heading on my column of numbers,
or even two or three cells with text above my number column, the code will
start the range for the autosum with the top numerical cell of my column.

Thanks,
Alan


Sub AutoSum()

Dim SumCell As Range
Dim FirstCell As Range
Dim LastCell As Range

Set SumCell = Selection

Set FirstCell = SumCell.Offset(-1).End(xlUp)

Set LastCell = SumCell.Offset(-1)

With SumCell
.FormulaR1C1 = "=sum(" & FirstCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ":" & LastCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ")"
End With

End Sub

Hi Achidsey,

One way of modifying your code might be:

'========================
Sub AutoSum2()

Dim SumCell As Range
Dim FirstCell As Range
Dim LastCell As Range
Dim Rng As Range
Dim rCell As Range
Dim RngFirstNumeric As Range

Set SumCell = Selection
Set FirstCell = SumCell.Offset(-1).End(xlUp)
Set LastCell = SumCell.Offset(-1)

Set Rng = Range(FirstCell, LastCell)

For Each rCell In Rng.Cells
If IsNumeric(rCell) Then
Set RngFirstNumeric = rCell
Exit For
End If
Next rCell

If Not RngFirstNumeric Is Nothing Then
With SumCell
.FormulaR1C1 = "=sum(" & RngFirstNumeric.Address _
(False, False, xlR1C1, RelativeTo:=.Item(1)) _
& ":" & LastCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ")"
End With
Else
'No numeric cell to sum!
End If

End Sub
'<<========================

As alternative approach, you could use the built in autosum control, as
demonstrated by Jim Rech in a NG post:

'========================
Sub DoAutoSum()
'// From: Jim Rech )Subject: AutoSum Toolbutton Execute
'// Newsgroups: microsoft.public.excel.programming Date: 2001-05-17 07:38:02
PST
'// In case anyone uses the Execute method with the AutoSum toolbar button I
'// just discovered it doesn'/// t work with Excel 2002 because the control
has
'// changed type. It'/// s now a msoControlSplitButtonPopup because it can
so
'// several math functions in addition to a Sum. So this is the way to
handle
'// it across versions I think:

Dim x As CommandBarControl
Set x = CommandBars.FindControl(ID:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If Selection.Cells.Count = 1 Then
x.Execute 'Again to exit edit mode if only one cell is selected
End If
End Sub
'<<========================


---
Regards,
Norman



"achidsey" (notmorespam) wrote in message
...
Excel Experts,

I wrote the below code which seems to be effective in putting an autosum
formula below a column of numbers such as this one. I select the cell
below
the numbers and run the macro.

A B
1
2 100
3 200
4 50
5

However, how can I modify it so if I have a heading on my column of
numbers,
or even two or three cells with text above my number column, the code will
start the range for the autosum with the top numerical cell of my column.

Thanks,
Alan


Sub AutoSum()

Dim SumCell As Range
Dim FirstCell As Range
Dim LastCell As Range

Set SumCell = Selection

Set FirstCell = SumCell.Offset(-1).End(xlUp)

Set LastCell = SumCell.Offset(-1)

With SumCell
.FormulaR1C1 = "=sum(" & FirstCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ":" & LastCell.Address(False, False,
xlR1C1, _
RelativeTo:=.Item(1)) & ")"
End With

End Sub

Norman,

Thanks very much for the assistance.

Alan


--
achidsey


"Norman Jones" wrote:

Hi Achidsey,

One way of modifying your code might be:

'========================
Sub AutoSum2()

Dim SumCell As Range
Dim FirstCell As Range
Dim LastCell As Range
Dim Rng As Range
Dim rCell As Range
Dim RngFirstNumeric As Range

Set SumCell = Selection
Set FirstCell = SumCell.Offset(-1).End(xlUp)
Set LastCell = SumCell.Offset(-1)

Set Rng = Range(FirstCell, LastCell)

For Each rCell In Rng.Cells
If IsNumeric(rCell) Then
Set RngFirstNumeric = rCell
Exit For
End If
Next rCell

If Not RngFirstNumeric Is Nothing Then
With SumCell
.FormulaR1C1 = "=sum(" & RngFirstNumeric.Address _
(False, False, xlR1C1, RelativeTo:=.Item(1)) _
& ":" & LastCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ")"
End With
Else
'No numeric cell to sum!
End If

End Sub
'<<========================

As alternative approach, you could use the built in autosum control, as
demonstrated by Jim Rech in a NG post:

'========================
Sub DoAutoSum()
'// From: Jim Rech )Subject: AutoSum Toolbutton Execute
'// Newsgroups: microsoft.public.excel.programming Date: 2001-05-17 07:38:02
PST
'// In case anyone uses the Execute method with the AutoSum toolbar button I
'// just discovered it doesn'/// t work with Excel 2002 because the control
has
'// changed type. It'/// s now a msoControlSplitButtonPopup because it can
so
'// several math functions in addition to a Sum. So this is the way to
handle
'// it across versions I think:

Dim x As CommandBarControl
Set x = CommandBars.FindControl(ID:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If Selection.Cells.Count = 1 Then
x.Execute 'Again to exit edit mode if only one cell is selected
End If
End Sub
'<<========================


---
Regards,
Norman



"achidsey" (notmorespam) wrote in message
...
Excel Experts,

I wrote the below code which seems to be effective in putting an autosum
formula below a column of numbers such as this one. I select the cell
below
the numbers and run the macro.

A B
1
2 100
3 200
4 50
5

However, how can I modify it so if I have a heading on my column of
numbers,
or even two or three cells with text above my number column, the code will
start the range for the autosum with the top numerical cell of my column.

Thanks,
Alan


Sub AutoSum()

Dim SumCell As Range
Dim FirstCell As Range
Dim LastCell As Range

Set SumCell = Selection

Set FirstCell = SumCell.Offset(-1).End(xlUp)

Set LastCell = SumCell.Offset(-1)

With SumCell
.FormulaR1C1 = "=sum(" & FirstCell.Address(False, False, xlR1C1, _
RelativeTo:=.Item(1)) & ":" & LastCell.Address(False, False,
xlR1C1, _
RelativeTo:=.Item(1)) & ")"
End With

End Sub