-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP

[CAPTGNVR]

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.

 

[Bob Phillips]

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]

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)

D/BOB, I am grateflul n thnks. But first time when u told itself, I
hv mentioned that when i use the last row property it does not work
bcos I have more data of numbers and temperatures below the tanks.
That means Below till b30 is for tank capacities and from b35 to b50
is ship gravity information and so on. To be frank I only gave one
scenario so that i could take it from there. Actually it is a huge
cumbersome task once the tank groups are changed. I have to change it
in about 5 to 7 sheets the new changed groups names, quantities,
temperatures, cubic capacities, metric ton, barrels calculations and
so on and at the end of each group I hv to put the sums for each group
and also to put fresh borders and make all the sums in bold letters.
So how to use 'lastrow' property if I have data below the tanks
designated area of b10 to b40. Pls Bob guide me and rushed for time -
I dont want to give up, I will sit up whole night and redo with ur
initial guidance and how to go about sugestion. Awaiting and
watching. Once again thnks.

 

[CAPTGNVR]

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)

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 Phillips]

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]

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)

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.

 

[CAPTGNVR]

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)

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.

 

[Tom Ogilvy]

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.

 

[CAPTGNVR]

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.

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.

 

[Tom Ogilvy]

Hope I explained what I want.

It doens't help me - still sounds like sumif. Maybe someone else can see
it.

 

[Tom Ogilvy]

I might add that some of the problem might be on the approach you are using.
I will admit that if you want to separate your groups and put a sum at the
bottom of the group, then sumif would not be the answer. However, it seems
that doing that would just raise further challenges if the sum is need to be
used somewhere else. If you have a data source in one location and then
summary type data in another, then perhaps that would be an approach more
conducive to your eventual purpose. An example would be that you could
summarize your data using a pivot table.

If you want to apply subtotals as you describe, then you could do that using
Data=>Subtotal

 

[Tom Ogilvy]

also,
if cells(rows.count,2).End(xlup).row

doesn't work then how about

cells(2,2).end(xldown).row

it would find the last row before the first break in your data.

 

[merjet]

Sub DoSums()
Dim sSubTotal As Single
Dim sTotal As Single
Dim ws As Worksheet
Dim iRow As Integer

Set ws = Sheets("Sheet1")
iRow = 10
Do
Do
sSubTotal = sSubTotal + ws.Cells(iRow, 2)
sTotal = sTotal + ws.Cells(iRow, 2)
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
ws.Cells(iRow, 2) = sSubTotal
sSubTotal = 0
Loop Until ws.Cells(iRow, 1) = "" And ws.Cells(iRow + 1, 1) = ""
ws.Cells(iRow, 2) = sTotal
End Sub

Hth,
Merjet

 

[CAPTGNVR]

Sub DoSums()
Dim sSubTotal As Single
Dim sTotal As Single
Dim ws As Worksheet
Dim iRow As Integer

Set ws = Sheets("Sheet1")
iRow = 10
Do
Do
sSubTotal = sSubTotal + ws.Cells(iRow, 2)
sTotal = sTotal + ws.Cells(iRow, 2)
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
ws.Cells(iRow, 2) = sSubTotal
sSubTotal = 0
Loop Until ws.Cells(iRow, 1) = "" And ws.Cells(iRow + 1, 1) = ""
ws.Cells(iRow, 2) = sTotal
End Sub

Hth,
Merjet

Thnk u Merget. It has been whole day trying to figure out and I have
to get the formula placed bcos every time the staff wont be running
the procedure. Once the group of tanks are changed I want to put the
formulas automatically thro VB. The problem is to put down exactly
what I want. I will take another shot tomoro and ur code is totally
different from the one i hv been trying. Will try yours too to see
what it does. let me try one more time to explain to u in a
different way.\

out of 24 tanks-- i might be asked load cargo into groups. So one
time it might be all the 24 tanks as one group. Then I have to have
only one total . Some time they might say load in 2 groups. then I
hv to split 24 tanks into two groups of say 10 tanks and 14 tanks. In
this case i need to have sub totals of each group and grand total of
group1 and group2. Like this they may say load in 4 groups. Then I
might need sub totals and grand total accly. So if u can take time to
see my first post where I have mentioned the way I require to be
done. Mr Bob has sugested the code. Only drawback is he is presuming
that the cells below are empty. I have other data below b40 and hence
using xldown or last cell does not work bcos it goes another may be 50
cells down bcos of data about 3 rows below b40.