On Feb 18, 10:44 pm, "Bob Phillips" wrote:
So does this do it

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Const SUM_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(40, TEST_COLUMN).End(xlUp).Row
iStart = 10
For i = 10 To iLastRow + 1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, SUM_COLUMN).Formula = "=SUM(" & SUM_COLUMN &
iStart & ":" & _
SUM_COLUMN & i - 1 & ")"
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"CAPTGNVR" wrote in message

oups.com...

On Feb 18, 9:25 pm, "Bob Phillips" wrote:
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Const SUM_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
iStart = 10
For i = 10 To iLastRow + 1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, SUM_COLUMN).Formula = "=SUM(" & SUM_COLUMN &
iStart & ":" & _
SUM_COLUMN & i - 1 & ")"
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"CAPTGNVR" wrote in message

groups.com...

For example I have say 24 tanks and their quantities in col_A and
col_B.. Based on the cargo nominations the group of tanks might
change. By using listbox I am able to get the selected tanks and their
quantities in col_A and col_B.
The difficulty I am facing is how to put it in VB -say- for the
following scenearios

say the quantites of the tanks start from row 10
1. If only one group with all 24 tanks as one group then the sum
formula in cell B34 should be sum(b10.b33)

2. If two group of tanks like first group 12 and second group 12 then
a sum forumla should be put in cell B22 as sum((b10.b21) and again
another sum formula at b35 as sum(b22.b34)

3. If three groups of tanks like first group 4, second group 8 and
third group 12 tanks then at the end of each group I need to put a sum
formula as mentioned in item-2 for each group.

4. If more than one group then after leaving a blank row after the
group is over one grand total sum formula adding the group like
=sum(b22+b35+b 44 or whatever is next group ending row)

col_A col_B
tanks

1 centr 1000 -starting from row 10
2 centr 5000
3 centr 4000
row 14 =sum(b10.b13) - end of first group and total

4 centr
5 centr
6 centr
row 18 =sum(b15.b17) - end of second group and total

7 centr
8 centr
9 centr
10 centr
11 centr
12 centr
row 25 =sum(b19.b24) - end of third group and total
row 26 =sum(sum at row 14+row18+row25) sum of all the groups.

Pls note I hv shown only 3 groups for example and it can even be 5 or
more groups which will be as announced by the company. How to
incorporate in the VB and to put the formulas and group totals
accordingly.

As I mentioned earlier I am due to proceed on vacation in another 4
days and would appreciate quick guidance to make it user friendly and
go. PLs help. I will be watching throughout the day constantly to work
and finish the program tonight itself.

D/BOB--
Sorry I said 'lastrow' instead of saying ".end(xlup) in ur line
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
Last time when I tried it failed bcos I have data below B40 and xlup
and xldown does not help.

Negative Bob. It does not bcos below Col_a and col_b I have some more
data and some blank rows and placing the sum formulas in the blank
cells. Feel bad in unable to explain my requirement. You see - it
might 4 groups or 3 or may be even 5. so basically I want to is based
on the number of groups, the group sum totals must come at the end of
each group based on the inputbox if it is 3 or 4 or 5 and finally the
last row to have grand total of group 1 to group x. What I asked is
only for one column but in real I have about another 15 column where I
will figure out using the offset formulas.