Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 oups.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 oups.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, 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 oups.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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 oups.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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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. 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 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. 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
Hope I explained what I want.
It doens't help me - still sounds like sumif. Maybe someone else can see it. -- Regards, Tom Ogilvy "CAPTGNVR" wrote in message oups.com... 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Hope I explained what I want. It doens't help me - still sounds like sumif. Maybe someone else can see it. -- Regards, Tom Ogilvy "CAPTGNVR" wrote in message oups.com... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Hope I explained what I want. It doens't help me - still sounds like sumif. Maybe someone else can see it. -- Regards, Tom Ogilvy "CAPTGNVR" wrote in message oups.com... 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
-PLS CONSIDER THIS AS URGENT! HOW TO PUT SUM FORMULA IN VB FOR A CHANGING MULTI CATEGORY OR GROUP
On Feb 19, 3:28 am, "merjet" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi-Level Category Labels | Charts and Charting in Excel | |||
How do I format multi-level category labels | Charts and Charting in Excel | |||
Multi level category labels | Excel Discussion (Misc queries) | |||
Multi-Level Category Gridlines | Charts and Charting in Excel | |||
representing multi category in a column chart | Charts and Charting in Excel |