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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)