UNABLE TO PUT THE SUM FORMULA IN VISUAL BASIC

[Guest]

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


ALFA 500
BETA 500
GAMA 500
TOP 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]

See response in excel.misc.

--
HTH

Bob Phillips

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

 

[Don Guillett]

And, PLEASE, do not type in all caps. It is considered to be very rude.

 

[smithb2]

re: Sum Function into VB

I am attempting to do the same thing..........

I want to insert the SUM function into a cell via code.

Here is what I have thus far:

ActiveCell.Offset(1, 0).Select
'created an int variable in which to store the counted # of cells to be inserted into the sum function.
i = 0



'Do loop performs test to determine how what integer to store in variable i.
'In this case, I selected the cell right below the bottom-most value to be included in the sum
'function by using the offset function. Hrs is the heading I have for that column so by saying loop
' while current cell value is not Hrs and incrementing the variable "i" after every completed loop, I now
'the # of cells stored in "i."
Do While ActiveCell.Value <> "Hrs"
ActiveCell.Offset(-1, 0).Select
i = i + 1
Loop



'For some reason, one that I wasn't about to go explore, I came out with a # one greater than what I
'expected, so I subtracted one.
i = i - 1

'Just as a test I put the value of "i" into a cell on the worksheet but made it negative.
ActiveSheet.Range("I46").Value = -(i)



'With the following statement I simply used the value stored in "i" to re-select the cell that is to house
'the sum function.
ActiveCell.Offset(Int(i + 1), 0).Select



'The following line is the magic line that inserts the sum function, however, there is a problem.
'FormulaR1C1 will not accept a variable has a member of the function.
ActiveCell.FormulaR1C1 = "=SUM(RC:R[-1]C)"
End Sub

So this is where I am at. The error is in run-time and is as follows: Application or user defined error. From the little bit of research I was able to do, I believe there is a way around this using error catching but at this point that is over my head.

I have a tread out in this newsgroup and will send a message your way if I make a break through.

Have a great day,
Smithb2

 

[smithb2]

solution!

As promised, Here is the solution I found to my dilemma:

As you may recall, as with your situation, I was trying to automatically determine, with a macro, how many columns were to be added and insert this info into the sum formula:

Option Explicit
Dim i As Integer

Sub HrsTotal()
i = 0

‘Do loop determines # of columns to store in integer variable (Hrs is the header for the ‘column

Do While ActiveCell.Value <> "Hrs"

ActiveCell.Offset(-1, 0).Select

i = i + 1

Loop

‘Following line uses the # stored in “i” to select cell in which to place sum function

ActiveCell.Offset(Int(i), 0).Select

‘In the following, I simply turn the value stored in “i” from positive to negative, in ‘preparation for sum formula.

i = -(i)

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

End Sub


The last line contains what I was unable to figure out, How to get the sum formula to accept the variable "i" as a value: " & i & "

Have a great day,
Smithb2