Macro question

[punter]

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

[Dave Peterson]

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).formular1c1 _
= "=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

[punter]

Thanks as always for the quick reply. I will kick it around and see if I can get it to work.

Eddie.

[punter]

This isn't working. Does anyone see anything wrong with the formula. What I'm trying to do is have it sum a column.

[Dave Peterson]

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

[punter]

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.

[Dave Peterson]

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

[punter]

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

[Dave Peterson]

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

[punter]

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.

[Dave Peterson]

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