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

[vba]
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
[/vba]

[jolivanes]

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

[/vba]

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

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

[/vba]

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

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

[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]

[VBA]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[/VBA]

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.
[VBA]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[/VBA]

[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.

[VBA]
Cells(Rows.Count, 1).End(xlUp).Row
[/VBA]

or something similar

[SamT]

Jolivanes,

I was lazy.

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

[mdmackillop]

Thanks to Ken Puls for this method
[VBA]
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[/VBA]