Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
Hi, I'm pretty new to the macro world but getting better. I am putting together a macro that does some calculations and format functions. One of the things I'm asking it to do is sum a column. The problem I'm running into is that the number of rows on the sheet will vary. If there are more rows on a sheet than the one that I used to record the macro it is summing in the wrong place. Here is how the sum function was recorded in the macro: ActiveCell.FormulaR1C1 = "=SUM(R[-4127]C:R[-2]C)" Question: How do I get excel to sum that column no matter how many cells there are? Currently it is summing everything up to 4127 and not beyond. I'm looking right now through the macro help but haven't tripped across the answer yet. Thanks as always, Eddie -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
Say you want to put the sum in column A under the last used cell. And you want
to sum from A2 to A(uponerow). dim LastRow as long with activesheet .cells(.rows.count,"A").end(xlup).offset(1,0).form ular1c1 _ = "=sum(r2c:r[-1]c)" end with R2C means Row 2 of the same column R[-1]C means one row up of the same column. punter wrote: Hi, I'm pretty new to the macro world but getting better. I am putting together a macro that does some calculations and format functions. One of the things I'm asking it to do is sum a column. The problem I'm running into is that the number of rows on the sheet will vary. If there are more rows on a sheet than the one that I used to record the macro it is summing in the wrong place. Here is how the sum function was recorded in the macro: ActiveCell.FormulaR1C1 = "=SUM(R[-4127]C:R[-2]C)" Question: How do I get excel to sum that column no matter how many cells there are? Currently it is summing everything up to 4127 and not beyond. I'm looking right now through the macro help but haven't tripped across the answer yet. Thanks as always, Eddie -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
Thanks as always for the quick reply. I will kick it around and see if I can get it to work. Eddie. -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
This isn't working. Does anyone see anything wrong with the formula. What I'm trying to do is have it sum a column. -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
I used column A to get the last row. Is that a problem?
You may want to post what you used. punter wrote: This isn't working. Does anyone see anything wrong with the formula. What I'm trying to do is have it sum a column. -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro question
I do thank you for the first formula and your second reply. I copied the formula that you gave me. I changed the "A" to "W". I end up getting an error. What I'm trying to do is sum column W which in this case has 4127 lines. The number of rows will change which is why I want the formula to add the range in that cell and give me the total sum. I would like the total sum to be calculated in the second blank cell after the last active cell in column W. Thank you again for your help. I'm having a real problem with this and just can't find the answer. It's such a simple concept to add all the active cells in a column and show me the answer but I can't find out how to get it done. -- punter ------------------------------------------------------------------------ punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044 View this thread: http://www.excelforum.com/showthread...hreadid=550982 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick Macro question - How to delete two rows then skip one - and repeat | Excel Discussion (Misc queries) | |||
Excel Macro Question | Excel Worksheet Functions | |||
using a macro question revisited | Excel Discussion (Misc queries) | |||
Excel Macro Question about Conditional Formatting | New Users to Excel | |||
Attn: Dave P. Question re Pix Calls via Macro | Excel Discussion (Misc queries) |