Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
DEAR PROGRAMMERS
PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
Try something like
activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ps.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
DEAR BOB
THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in glegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
The formula I gave you will sum from row 1 to the row above the one that you
put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message oups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in glegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
Dear Bob
As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names andtheir bonus
I'm not Bob, but there might be better ways of getting totals than inserting a
blank row and then adding =sum() formulas. If you have a key column that contains an identifier per group, you could sort by that group and then apply Data|Subtotals to that range. Your life in excel could be much easier if you use some of the built-in tools that it provides. But... If your values in column B are values--not formulas, you could try a routine like: Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "no Constants in that range" Exit Sub End If For Each myArea In myRng.Areas With myArea .Cells(.Cells.Count).Offset(1, 0) = "=sum(" & .Address(0, 0) & ")" End With Next myArea End Sub CAPTGNVR wrote: Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
Thank you for turning the caps off.
I would still work up as I previously suggested. It is relatively easy to work out the previous blank in a worksheet, just use Activecell.End(xlUp).Offset(-1,0) which is basically saying, go up from where I am until the previous row is blank (End(xlUp), then one more to that blank (Offset(-1,0)). Adding .Row at the end returns that row number. This can be used in setting the formula like so With ActiveCell .FormulaR1C1 = "=SUM(R" & .End(xlUp).Row & "C:R[-1]C)" End With However, from your description, you seem to want to set these totals for many blocks, so I would use that similar technique in a loop, without using ACtivecell, but indexing directly into the cells Dim i As Long Dim iRow As Long With ActiveSheet i = Cells(.Rows.Count, "B").End(xlUp).Row + 1 Do While i 0 iRow = .Cells(i - 1, "B").End(xlUp).Row .Cells(i, "B").FormulaR1C1 = "=SUM(R" & iRow & "C:R[-1]C)" i = iRow - 1 Loop End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message oups.com... Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
TO MR. BOB N DAVE
Thank you Sirs, I figured it out from your guided steps and got exactly what I wanted to do. I will move on to the next thread. On Jan 28, 11:57 pm, "CAPTGNVR" wrote: Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
DEAR DAVE I did not know that i cant rate the post. Now i have rated it as excellent bcos it gave me an insight to work on these group totals and i have taken off with the clues you have given-thnks Can i ask some more of my basic doubts or i must go thro only the threads?? On Jan 29, 1:08 am, Dave Peterson wrote: I'm not Bob, but there might be better ways of getting totals than inserting a blank row and then adding =sum() formulas. If you have a key column that contains an identifier per group, you could sort by that group and then apply Data|Subtotals to that range. Your life in excel could be much easier if you use some of the built-in tools that it provides. But... If your values in column B are values--not formulas, you could try a routine like: Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "no Constants in that range" Exit Sub End If For Each myArea In myRng.Areas With myArea .Cells(.Cells.Count).Offset(1, 0) = "=sum(" & .Address(0, 0) & ")" End With Next myArea End Sub CAPTGNVRwrote: Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM-- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
DEAR BOB
I did not know that i cant rate the post. Now i have rated it as excellent bcos it gave me an insight to work on these group totals and i have taken off with the clues you have given-thnks Can i ask some more of my basic doubts or i must go thro only the threads?? On Jan 29, 1:24 am, "Bob Phillips" wrote: Thank you for turning the caps off. I would still work up as I previously suggested. It is relatively easy to work out the previous blank in a worksheet, just use Activecell.End(xlUp).Offset(-1,0) which is basically saying, go up from where I am until the previous row is blank (End(xlUp), then one more to that blank (Offset(-1,0)). Adding .Row at the end returns that row number. This can be used in setting the formula like so With ActiveCell .FormulaR1C1 = "=SUM(R" & .End(xlUp).Row & "C:R[-1]C)" End With However, from your description, you seem to want to set these totals for many blocks, so I would use that similar technique in a loop, without using ACtivecell, but indexing directly into the cells Dim i As Long Dim iRow As Long With ActiveSheet i = Cells(.Rows.Count, "B").End(xlUp).Row + 1 Do While i 0 iRow = .Cells(i - 1, "B").End(xlUp).Row .Cells(i, "B").FormulaR1C1 = "=SUM(R" & iRow & "C:R[-1]C)" i = iRow - 1 Loop End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote inmessagenews:1169991338.861791.232250@s48g2000cws .googlegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wroteinmessagenews:1169982897.438451.87730@h3g2000 cwc.googlegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names andtheir bonus
If you have a question related to your own existing thread, it's usual to ask
there. But unless the original poster already has a response, I wouldn't "steal" that thread. Instead, I would post a brand new question. CAPTGNVR wrote: DEAR DAVE I did not know that i cant rate the post. Now i have rated it as excellent bcos it gave me an insight to work on these group totals and i have taken off with the clues you have given-thnks Can i ask some more of my basic doubts or i must go thro only the threads?? On Jan 29, 1:08 am, Dave Peterson wrote: I'm not Bob, but there might be better ways of getting totals than inserting a blank row and then adding =sum() formulas. If you have a key column that contains an identifier per group, you could sort by that group and then apply Data|Subtotals to that range. Your life in excel could be much easier if you use some of the built-in tools that it provides. But... If your values in column B are values--not formulas, you could try a routine like: Option Explicit Sub testme() Dim myRng As Range Dim myArea As Range With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "no Constants in that range" Exit Sub End If For Each myArea In myRng.Areas With myArea .Cells(.Cells.Count).Offset(1, 0) = "=sum(" & .Address(0, 0) & ")" End With Next myArea End Sub CAPTGNVRwrote: Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote ooglegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM-- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to put 'SUM' formula in VB after picking up certain names and their bonus
I echo Dave's statements.
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message ups.com... DEAR BOB I did not know that i cant rate the post. Now i have rated it as excellent bcos it gave me an insight to work on these group totals and i have taken off with the clues you have given-thnks Can i ask some more of my basic doubts or i must go thro only the threads?? On Jan 29, 1:24 am, "Bob Phillips" wrote: Thank you for turning the caps off. I would still work up as I previously suggested. It is relatively easy to work out the previous blank in a worksheet, just use Activecell.End(xlUp).Offset(-1,0) which is basically saying, go up from where I am until the previous row is blank (End(xlUp), then one more to that blank (Offset(-1,0)). Adding .Row at the end returns that row number. This can be used in setting the formula like so With ActiveCell .FormulaR1C1 = "=SUM(R" & .End(xlUp).Row & "C:R[-1]C)" End With However, from your description, you seem to want to set these totals for many blocks, so I would use that similar technique in a loop, without using ACtivecell, but indexing directly into the cells Dim i As Long Dim iRow As Long With ActiveSheet i = Cells(.Rows.Count, "B").End(xlUp).Row + 1 Do While i 0 iRow = .Cells(i - 1, "B").End(xlUp).Row .Cells(i, "B").FormulaR1C1 = "=SUM(R" & iRow & "C:R[-1]C)" i = iRow - 1 Loop End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in ooglegroups.com... Dear Bob As instructed 'caps lock is off". Aye Aye Sir. I tried what u sugested. There is progress. But unwanted results. It is giving the formula in each group from top to the blank cell. I have plenty groups on the same column with one blank space between each group where the sum formula for the respective group to fit it. For example: first group b5:b10 ; second group b12.b17; third group b19.b29 How to write the sum formula using cells range in VB code after the cell is activated in VB at b11, b18 and b30. Finally in the excel sheet the formula at b11 should be like: =sum(b5.b10); and at b18: =sum(b12.b17) and at b30: =sum(b19.b29). The VB code is necessary bcos the group numbers will not be constant and may increase or decrease. Very eagerly awaiting to complete the task in hand. To tell u the fact-- the actual calculation i am doing is for my ship where i have four kinds of cargo tanks. I am trying to automatically sum the groups once the tanks are rearranged when the new cargo is alloted. Thanks for ur time and I am obligated for ur assistance. BRGDS/CAPT GN VENKAT RAJARAM On Jan 28, 10:22 pm, "Bob Phillips" wrote: The formula I gave you will sum from row 1 to the row above the one that you put the formula in, so you don't need to know. can you turn your caps lock off, it is annoying. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote inmessagenews:1169991338.861791.232250@s48g2000cws .googlegroups.com... DEAR BOB THANKS FOR QUICK RESPONSE. THE PROBLEM IS I DONT KNOW HOW MANY ROWS I HAVE TO SUM. I CAN GET THE NUMBER OF ROWS USING ROW COUNT. I WANT TO USE THIS VARIABLE IN THE SUM FORMULA IN VB (ACTIVECELL.FORMULAR1C1="SUM(ROWVAR COLVAR: ROWVAR2 COLVAR2)". On Jan 28, 8:50 pm, "Bob Phillips" wrote: Try something like activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) wroteinmessagenews:1169982897.438451.87730@h3g2000 cwc.googlegroups.com... DEAR PROGRAMMERS PLS HELP ME IN THE FOLLOWING: USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR SO GOOD. CHARLIE 100 ROMEO 200 TOM 100 1STGROUP (how to put sum formula in VB for this group) ALFA 500 BETA 500 GAMA 500 TOP GROUP (how to put sum formula in VB for this group) LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY USING LISTBOX AND VB. I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP AND SO ON. PLS HELP WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10). THANKING YOU CAPT VENKAT RAJARAM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|