how to put 'SUM' formula in VB after picking up certain names and their bonus

[captgnvr]

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

 

[Bob Phillips]

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)

 

[CAPTGNVR]

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)".

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)

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

 

[Bob Phillips]

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)

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)".

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)

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

 

[CAPTGNVR]

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

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)

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)".

Try something like
activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

addy)

<[email protected]> wrote inmessagenews:[email protected]...

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]

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

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

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)

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)".

Try something like

activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Bob Phillips

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



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

 

[Bob Phillips]

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)

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

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)

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)".

Try something like

activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Bob Phillips

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

my
addy)

<[email protected]> wrote

inmessage

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

 

[CAPTGNVR]

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.

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

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.
Bob Phillips

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

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)".
Try something like
activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

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

 

[CAPTGNVR]

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??

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

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)
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)".
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)
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

 

[CAPTGNVR]

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??

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)

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

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.

addy)

"CAPTGNVR" <[email protected]> wrote inmessagenews:[email protected]...

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)".
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)
<[email protected]> wroteinmessagenews:[email protected]...

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]

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.

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??

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

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.
Bob Phillips

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

"CAPTGNVR" <[email protected]> wrote in messagenews:[email protected]...

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)".

Try something like

activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Bob Phillips

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

<[email protected]> wrote inmessagenews:[email protected]...

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

 

[Bob Phillips]

I echo Dave's statements.

--
HTH

Bob Phillips

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

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??

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)

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

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.
Bob Phillips

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

my
addy)

"CAPTGNVR" <[email protected]> wrote

inmessage

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)".

Try something like

activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail

in
my

addy)

<[email protected]>

wroteinmessage

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