Getting sum of unknown number of rows

[coolman]

Hi I have been trying to get the total sum of unknown number of rows. However i could not get it to work from my code below. Any advice of how to get it work?

Private Sub CmdNextSection_Click()


lastrow = ActiveSheet.Range("b" & Rows.Count).End(xlUp).Row


With Cells(lastrow + 1, 5)
.Value = "Subtotal"
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With


With Cells(lastrow + 2, 5)
.Value = "GST"
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With


With Cells(lastrow + 3, 5)
.Value = "Grand Total"
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With


Subtotal = 0
For i = 19 To lastrow
With Cells(lastrow + 1, 6)
.Value = Subtotal + Range("i,6")
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With
Next i
End Sub

 

[Fluff]

Try this in place of your loop

With Cells(20000 + 1, 6)
.FormulaR1C1 = "=sum(r19c:r[-1]c)"
[COLOR=#ff0000].Value = .Value[/COLOR]
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With

If you want the formula rather than a hard value, remove the line in red

 

[coolman]

ok noted. yes it works. thank you so much.

I have another thought

if i want the value from the userform to input into the activesheet and content page worksheet, is there any way to do it?
Also the content page will have to add new row on its own

Try this in place of your loop

With Cells(20000 + 1, 6)
.FormulaR1C1 = "=sum(r19c:r[-1]c)"
[COLOR=#ff0000].Value = .Value[/COLOR]
.WrapText = False
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
.Font.Bold = True
End With

If you want the formula rather than a hard value, remove the line in red

 

[Fluff]

First off, just realised I forgot to put last row back in. The first line should read

With Cells(lastrow+ 1, 6)

I don't really understand your new request, but is this what you're after.

Range("A" & lastrow + 1).Value=Textbox1.Value