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

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

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
(E-Mail Removed)
"vivi" <(E-Mail Removed)> wrote in message
news:4D420A74-9682-4A69-8E30-(E-Mail Removed)...
> 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

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

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

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
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "vivi" <(E-Mail Removed)> wrote in message
> news:4D420A74-9682-4A69-8E30-(E-Mail Removed)...
>> 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
>