Sum get error

[rianz]

hi i got the error message when i run this code, the problem is sum of each column not count. anyone know where the error is?

thx


Sub jumlah()

Dim wsD As Worksheet
Dim wsT As Worksheet
Dim FinalRow As Long
Dim FinalCol As Long

Set wsD = ThisWorkbook.Worksheets("data")
Set wsT = Sheets.Add(After:=Sheets(Worksheets.Count))
wsT.Name = "total"

wsT.Range("A1:Z5000").ClearContents

FinalRow = wsD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = wsD.Cells(1, Columns.Count).End(xlToLeft).Column

wsD.Range(wsD.Cells(1, 1), wsD.Cells(FinalRow, FinalCol)).Copy _
Destination:=wsT.Range("A4")

FinalRow = wsT.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = wsT.Cells(4, Columns.Count).End(xlToLeft).Column

wsT.Cells(FinalRow + 2, 1).Value = "Total"
wsT.Range(Cells(FinalRow + 2, 2), Cells(FinalRow + 2, FinalCol)) _
.FormulaR1C1 = "=Sum(R[" & -FinalRow + 3 & "]C:R[-2]C)"

End Sub

[jolivanes]

.FormulaR1C1 = "=Sum(R[" & -FinalRow + 3 & "]C:R[-2]C)"



What happens when you take the - (minus) out?


.FormulaR1C1 = "=Sum(R[" & FinalRow + 3 & "]C:R[-2]C)"



Edit: Forget what I said above. I see now what you're trying to accomplish. Not what I mentioned above.

[rianz]

i got the same error....

[jolivanes]

Maybe try this instead


.FormulaR1C1 = "=Sum(R[" & FinalRow * -1 + 3 & "]C:R[-2]C)"

[rianz]

hey jolivanes thx for the answer but i still got the error message, i upload the file i have

[jolivanes]

This maybe? (see attached)

[mdmackillop]

Sub Hello2()
Dim C As Range
Set C = Cells(1, 1).End(xlToRight).End(xlDown)
Cells(C.Row + 2, 1) = "Total"
Cells(C.Row + 2, 2).Resize(, C.Column - 1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
End Sub

BTW I would use the simpler form
& 3-FinalRow &

[SamT]

This worked for me on a small table. I renamed some vars because I want this in my Personal.xls.
Sub CopyAndSum()

Dim DataSht As Worksheet
Dim TotalSht As Worksheet
Dim FinalRow As Long
Dim FinalCol As Long
Dim SumRng As Range

Set DataSht = ThisWorkbook.Worksheets("data")
Set TotalSht = Sheets.Add(After:=Sheets(Worksheets.Count))
TotalSht.Name = "total"

DataSht.UsedRange.Copy
TotalSht.Range("A4").PasteSpecial (xlPasteValues)

Set SumRng = TotalSht.Range("A65000").End(xlUp).Offset(1, 0)
SumRng.Value = "Total"
SumRng.Offset(0, 1).Formula = "=Sum(" & "B4:B" & CStr(SumRng.Row - 1) & ")"
SumRng.Offset(0, 1).Resize(, (SumRng.Offset(-1, 0).End(xlToRight).Column) - 1).FillRight
End Sub

[jolivanes]

@mdmackillop
Re: "& 3-FinalRow &"

It has to hit me on the head to see it I Guess.
Too absorbed in what was supplied.

Thanks Mark


@SamT
Range("A65000").End(xlUp) is a leftover from excel 2003.
I would change it to fit 2007 and on.


Cells(Rows.Count, 1).End(xlUp).Row


or something similar

[SamT]

Jolivanes,

I was lazy. :sleeping:

I will let the OP do that.
When I finish the copy in my Personal.xls, I will do it right. :work:

[mdmackillop]

Thanks to Ken Puls for this method

Sub CellAbove() 'Run once, unless you add more sheets
'http://www.excelguru.ca/content.php?142-Always-Refer-to-the-Cell-Above
Dim sh As Worksheet
Dim cel As Range

Set cel = Selection
For Each sh In Worksheets
Application.Goto sh.Range("A2")
ActiveWorkbook.Names.Add Name:="CellAbove", RefersToR1C1:="=" & ActiveSheet.Name & "!R[-1]C"
Next
Application.Goto cel
End Sub


Sub Hello3()
ActiveCell.FormulaR1C1 = "=SUM(R1C:CellAbove)"
End Sub