Autosum function

I have a list of numbers with all in same column. When clicking normal
autosum function, Excel determines the sum range for me, including subtotals
& grandtotals.
How can this happen with same effect in VBA language so that I need not
define the sum ranges (variable).

E.g.

1
2
3
click autosum : =sum(1:3)
4
5
click autosum : =sum(4:5)
click autosum : =sum(above subtotals)

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim tmpSub As Double, tmpTotal As Double

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
tmpSub = Range("A1").Value
For i = 2 To iLastRow + 1
If Cells(i, "A") <> "" Then
tmpSub = tmpSub + Cells(i, "A").Value
Else
Cells(i, "A").Value = tmpSub
tmpTotal = tmpTotal + tmpSub
tmpSub = 0
End If
Next i
Cells(i, "A").Value = tmpTotal

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Frankie" <[email protected]> wrote in message
news:[email protected]...
> I have a list of numbers with all in same column. When clicking normal
> autosum function, Excel determines the sum range for me, including
subtotals
> & grandtotals.
> How can this happen with same effect in VBA language so that I need not
> define the sum ranges (variable).
>
> E.g.
>
> 1
> 2
> 3
> click autosum : =sum(1:3)
> 4
> 5
> click autosum : =sum(4:5)
> click autosum : =sum(above subtotals)

If you want formulae, try

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim tmp As String

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 2 To iLastRow + 1
If Cells(i, "A") = "" Then
Cells(i, "A").Formula = "=SUM(A" & iStart & ":A" & i - 1 & ")"
tmp = tmp & "A" & i & ","
iStart = i + 1
End If
Next i
Cells(i, "A").Formula = "=SUM(" & Left(tmp, Len(tmp) - 1) & ")"

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim tmpSub As Double, tmpTotal As Double
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> tmpSub = Range("A1").Value
> For i = 2 To iLastRow + 1
> If Cells(i, "A") <> "" Then
> tmpSub = tmpSub + Cells(i, "A").Value
> Else
> Cells(i, "A").Value = tmpSub
> tmpTotal = tmpTotal + tmpSub
> tmpSub = 0
> End If
> Next i
> Cells(i, "A").Value = tmpTotal
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Frankie" <[email protected]> wrote in message
> news:[email protected]...
> > I have a list of numbers with all in same column. When clicking normal
> > autosum function, Excel determines the sum range for me, including
> subtotals
> > & grandtotals.
> > How can this happen with same effect in VBA language so that I need not
> > define the sum ranges (variable).
> >
> > E.g.
> >
> > 1
> > 2
> > 3
> > click autosum : =sum(1:3)
> > 4
> > 5
> > click autosum : =sum(4:5)
> > click autosum : =sum(above subtotals)
>
>

Hi Bob,

It solves part of my problem as it only gives one subtotal and one grandtotal.
In fact, the spreadsheet has 2 columns, column "A" contains wordings "total
/ subtotal" and column "B" list of values. My job is to find where the
total/subtotal located in column "A" and create an autosum formula in
corresponding cell in column "B". Since the sum ranges vary, I can only
locate the total/subtotal manually in column "A" & click autosum in column
"B" where the sum ranges appear automatically. However, I don't know how to
automate this process in VBA. Eager to hear your advice on it.

Rgds,

"Bob Phillips" wrote:

> If you want formulae, try
>
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim iStart As Long
> Dim tmp As String
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> iStart = 1
> For i = 2 To iLastRow + 1
> If Cells(i, "A") = "" Then
> Cells(i, "A").Formula = "=SUM(A" & iStart & ":A" & i - 1 & ")"
> tmp = tmp & "A" & i & ","
> iStart = i + 1
> End If
> Next i
> Cells(i, "A").Formula = "=SUM(" & Left(tmp, Len(tmp) - 1) & ")"
>
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim tmpSub As Double, tmpTotal As Double
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > tmpSub = Range("A1").Value
> > For i = 2 To iLastRow + 1
> > If Cells(i, "A") <> "" Then
> > tmpSub = tmpSub + Cells(i, "A").Value
> > Else
> > Cells(i, "A").Value = tmpSub
> > tmpTotal = tmpTotal + tmpSub
> > tmpSub = 0
> > End If
> > Next i
> > Cells(i, "A").Value = tmpTotal
> >
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Frankie" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a list of numbers with all in same column. When clicking normal
> > > autosum function, Excel determines the sum range for me, including
> > subtotals
> > > & grandtotals.
> > > How can this happen with same effect in VBA language so that I need not
> > > define the sum ranges (variable).
> > >
> > > E.g.
> > >
> > > 1
> > > 2
> > > 3
> > > click autosum : =sum(1:3)
> > > 4
> > > 5
> > > click autosum : =sum(4:5)
> > > click autosum : =sum(above subtotals)
> >
> >
>
>
>