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

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

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

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

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

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

I think it's time to post your code.

punter wrote:

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

--

Dave Peterson

Option Explicit

Dim LastRow As Long
With ActiveSheet
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).form ular1c1 _
= "=sum(r2c:r[-1]c)"
End With


That is what I put in the macro.

Thanks


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=550982

That doesn't look like you changed the A to W.

Option Explicit
Sub myMacro()

Dim LastRow As Long
With ActiveSheet
.Cells(.Rows.Count, "W").End(xlUp).Offset(1, 0).FormulaR1C1 _
= "=sum(r2c:r[-1]c)"
End With

End Sub



punter wrote:

Option Explicit

Dim LastRow As Long
With ActiveSheet
Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).form ular1c1 _
= "=sum(r2c:r[-1]c)"
End With

That is what I put in the macro.

Thanks

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

Thank you so much Dave. I really did change it to "W" but didn't paste
it that way into the website. When I took your last example and placed
it back into the macro it worked perfectly. This is a piece of a larger
puzzle that will end up saving me a lot of time.

Thank you again.

Eddie.


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=550982

Glad you got it working.

punter wrote:

Thank you so much Dave. I really did change it to "W" but didn't paste
it that way into the website. When I took your last example and placed
it back into the macro it worked perfectly. This is a piece of a larger
puzzle that will end up saving me a lot of time.

Thank you again.

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