On Feb 18, 11:42 pm, "Tom Ogilvy" wrote:
in either vba or in the worksheet directly, why wouldn't you use

=sumif(rngA,"tank*",rngB)

or if the identifier contains the word tank

=sumif(rngA,"*tank*",rngB)

It sounds like you have a fixed range from which you only want to sum column
B for rows in column A beginning with Tank or some other identifier.

--
Regards,
Tom Ogilvy

"CAPTGNVR" wrote in message

oups.com...





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

groups.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.

Bob- now I am trying to make the tank capacities as one range and use
countA to get the number of rows instead of end(xlup). If u hv some
time pls explain iLastRow = .Cells(.Rows.Count,
TEST_COLUMN).End(xlUp).Row -- with regard to end(xlup) bcos i do not
find this in help.

THNK U TOM.
Pls read my first post wherein I hv given details about the tank
distribution. I do agree that within given range of a10 to b40 is the
range for the tank description and capacities. The problem is the
company will keep changing the set of tanks. So I wrote a program to
select the new group of tanks and their capacities using listbox and
other VB commands to get the capacities. The next step is I have to
know the totals in each group and then grand total. Once i get the
help in figuring out this then I will develop from there using offset
method and fill in the formulas in 5 sheets and in as many as 20
colums as the complete data will change when the tanks are changed.
Hope I explained what I want.