Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sum to the columns at the last row
Hi there, I am using this code here but it doesn't give me the result I
desire as I have a 36 months starting from Column V and data starts at row 7. The problem is that the starting point is always the same, but my end point varies depending the number of entries. I would like to add a formula to the end of each month. The code I am using is: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)" End Sub Instead of calculating the column itself, it calculates from the start point to the last cell that has a number Can you help please? Thanks a lot Viv |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sum to the columns at the last row
Sub Marco()
Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")" End Sub -- If this post helps click Yes --------------- Jacob Skaria "vivi" wrote: Hi there, I am using this code here but it doesn't give me the result I desire as I have a 36 months starting from Column V and data starts at row 7. The problem is that the starting point is always the same, but my end point varies depending the number of entries. I would like to add a formula to the end of each month. The code I am using is: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)" End Sub Instead of calculating the column itself, it calculates from the start point to the last cell that has a number Can you help please? Thanks a lot Viv |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sum to the columns at the last row
Hi There
Thanks for this, this work perfectly At the moment i just fix to column c as I am just playing around with it I have actually 250 columns to insert this formula Do you have a suggestion to loop this instead of writing 250 lines of code for each column? I thought of using similar code to set the column as a variant and loop it by adding 1 each time. But i keep having an error and refuse to let me go further !!! Thanks a lot "Jacob Skaria" wrote: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")" End Sub -- If this post helps click Yes --------------- Jacob Skaria "vivi" wrote: Hi there, I am using this code here but it doesn't give me the result I desire as I have a 36 months starting from Column V and data starts at row 7. The problem is that the starting point is always the same, but my end point varies depending the number of entries. I would like to add a formula to the end of each month. The code I am using is: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)" End Sub Instead of calculating the column itself, it calculates from the start point to the last cell that has a number Can you help please? Thanks a lot Viv |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sum to the columns at the last row
Forgot to give you what I've written, it didn't give me an error but it takes
a long time and froze excel, but it should be short as only a small test ...I had to stop it by pressing esc Sub Marco() Dim lastrow2 As Range Do Until qcol = 20 qcol = 8 ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, [qcol]).End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, [qcol]).End(xlUp). _ Offset(1, 0) lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")" qcol = qcol + 1 Loop End Sub "Jacob Skaria" wrote: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")" End Sub -- If this post helps click Yes --------------- Jacob Skaria "vivi" wrote: Hi there, I am using this code here but it doesn't give me the result I desire as I have a 36 months starting from Column V and data starts at row 7. The problem is that the starting point is always the same, but my end point varies depending the number of entries. I would like to add a formula to the end of each month. The code I am using is: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)" End Sub Instead of calculating the column itself, it calculates from the start point to the last cell that has a number Can you help please? Thanks a lot Viv |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sum to the columns at the last row
This will use col C as the longest row and put the sum formula for c:H.
Modify to suit If c is not the longest row, use another. Sub sumcolc() lr = Cells(Rows.Count, "c").End(xlUp).Row Cells(lr + 1, "c").Resize(, 6).Formula = _ "=sum(c1:c" & lr & ")" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... This sums c7:v? whatever the last row in col C is. Does it below last row in col C Sub sumcolc() lr = Cells(Rows.Count, "c").End(xlUp).Row Cells(lr + 1, "c").Formula = "=sum(c7:v" & lr & ")" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Hi there, I am using this code here but it doesn't give me the result I desire as I have a 36 months starting from Column V and data starts at row 7. The problem is that the starting point is always the same, but my end point varies depending the number of entries. I would like to add a formula to the end of each month. The code I am using is: Sub Marco() Dim lastrow2 As Range ActiveWorkbook.Names.Add Name:="lastrow4", _ RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp) Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _ Offset(1, 0) lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)" End Sub Instead of calculating the column itself, it calculates from the start point to the last cell that has a number Can you help please? Thanks a lot Viv |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
button to seach columns for blank cells, then sort by two columns | Excel Programming | |||
Help in code Steve G wrote to move data from 4 columns to 21 columns | Excel Programming | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) |