Macro question

P

punter

Hi,

I'm pretty new to the macro world but getting better. I am puttin
together a macro that does some calculations and format functions. On
of the things I'm asking it to do is sum a column. The problem I'
running into is that the number of rows on the sheet will vary. I
there are more rows on a sheet than the one that I used to record th
macro it is summing in the wrong place. Here is how the sum functio
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 man
cells there are? Currently it is summing everything up to 4127 and no
beyond. I'm looking right now through the macro help but haven'
tripped across the answer yet.

Thanks as always,

Eddi
 
D

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.
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
Click to expand...
 
P

punter

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

Eddie.
 
P

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
 
D

Dave Peterson

I used column A to get the last row. Is that a problem?

You may want to post what you used.
 
P

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".
end up getting an error. What I'm trying to do is sum column W whic
in this case has 4127 lines. The number of rows will change which i
why I want the formula to add the range in that cell and give me th
total sum. I would like the total sum to be calculated in the secon
blank cell after the last active cell in column W.

Thank you again for your help. I'm having a real problem with this an
just can't find the answer. It's such a simple concept to add all th
active cells in a column and show me the answer but I can't find ou
how to get it done
 
D

Dave Peterson

I think it's time to post your code.
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.
Click to expand...
 
P

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.

Thank
 
D

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


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
Click to expand...
 
P

punter

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

Thank you again.

Eddie
 
D

Dave Peterson

Glad you got it working.
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.
Click to expand...
 
Top