PDA

View Full Version : Sum get error



rianz
04-03-2013, 10:01 PM
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
04-03-2013, 10:33 PM
.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
04-03-2013, 10:42 PM
i got the same error....

jolivanes
04-03-2013, 10:50 PM
Maybe try this instead


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

rianz
04-03-2013, 11:16 PM
hey jolivanes thx for the answer but i still got the error message, i upload the file i have

jolivanes
04-04-2013, 01:37 PM
This maybe? (see attached)

mdmackillop
04-04-2013, 02:36 PM
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
04-04-2013, 05:27 PM
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
04-04-2013, 07:14 PM
@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
04-04-2013, 07:26 PM
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
04-05-2013, 09:32 AM
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