Solved: Total

[maninjapan]

I need a simple macro to total all the data in Column C.....
It would find the last cell with data entered then return the total in column D, 2 rows below the final entry in C,
Ive Made an attempt but its well off the mark........

[vba]Sub Total()

Dim C_Range As Integer

' Attempt to find the last row with data

C_Range = Sheet3.Range("C1").CurrentRegion.Rows.Count


' attempt to put the total in Row D, 2 cells below the entered Data
Sheet3.Cells(NextRow + 1, 4) =Total(Sheet3.Range(Sheet3.Cells(3,1),Sheet3.Range(Sheet3.Cells(3,C_Range)) "

End Sub[/vba]

[GTO]

Hopefully this'll work for you. Have to shut down...

Have a great day,

Mark

[VBA]Option Explicit

Sub Total()

Dim C_Range As Integer

' Attempt to find the last row with data
C_Range = Sheet3.Range("C65536").End(xlUp).Row


' attempt to put the total in Row D, 2 cells below the entered Data
Sheet3.Cells(C_Range + 2, 4).Value = _
Application.WorksheetFunction.Sum(Sheet3.Range(Cells(1, 3), Cells(C_Range, 3)))
End Sub
[/VBA]

[Bob Phillips]

I would inject a formula, in case any values are updated

[vba]

Sub Total()
Dim LastRow As Long

With Sheet3

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Cells(LastRow + 2, "D").Formula = "=SUM(C1:C" & LastRow & ")"
End With
End Sub
[/vba]

[GTO]

I would too, if I woulda been smart enough to think of that... (Nice catch and thanks Bob.)

Mark

[maninjapan]

guys thanks, that works perfectly.

I have tried to add this total and a cut/paste into one click.
The idea is to create a subtotal and then transfer the data over to the main page. (Im sure there is a way to do it all on one page but thats ok for now...)
The problem is from the second transfer the totals on the main page get out of whack. is there a way to ensure that each process happens in the order specified? Or would I need to split it up into 2 separate events?
[vba]

Private Sub CommandTraderTotal_Click()

Copy Data over to main sheet

Dim sh1 As Worksheet
Dim LastRow As Long
Dim NextRow As Long
Dim LastColumn As Long


With Sheet3

LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Cells(LastRow + 0, "L").Formula = "=SUM(G1:G" & LastRow & ")"
End With


Set sh1 = Worksheets("Sheet1")
NextRow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row

With Sheets("Sheet3")
LastRow = GetLastRow
LastColumn = GetLastColumn
.Range("A1").Resize(LastRow, LastColumn).Cut sh1.Cells(NextRow, 1)
End With
End Sub

'-----------------------------------------------------------------
Function GetLastRow() As Long
'-----------------------------------------------------------------
GetLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function

'-----------------------------------------------------------------
Function GetLastColumn() As Long
'-----------------------------------------------------------------
GetLastColumn = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End Function



[/vba]

[Bob Phillips]

Can we have a workbook to se it in action?

[maninjapan]

sure, start from the button on sheet 1.
add data to userform then Next Contract, then Trader Total
The 1s total goes through fine. The 2nd total is where the problem starts

[Bob Phillips]

In the TraderTotal code you are incrementing the lastrow on Sheet3 by 0 (sic!), and not incrementing the NextRow on Sheet1 at all.