PDA

View Full Version : [SOLVED] Sum the column



parscon
09-09-2013, 01:52 AM
This code will sum the numbers that start from row E on column 20 , now I have a problem that when I have only 1 row that mean data only on E20 it will not work . that mean If just I have 1 row write the number of E20 on E21.

Hope you will understand what I need .

Thank you.


Sub TEST()

Dim Rng As Range
Dim c As Range
Set Rng = Range("E20:E" & Range("E20").End(xlDown).Row)
Set c = Range("E20").End(xlDown).Offset(1, 0)
c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
End Sub

JKwan
09-09-2013, 06:40 AM
May not be the best but it does the job....


Sub test()
Dim Rng As Range
Dim c As Range

On Error GoTo HandleError
Set Rng = Range("E20:E" & Range("E20").End(xlDown).Row)
Set c = Range("E20").End(xlDown).Offset(1, 0)
c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
Exit Sub

HandleError:
If Err.Number = 1004 Then
Range("E21") = Range("E20")
Else
Msgbox "Error Encountered - " & Err.Description
End If
End Sub

parscon
09-09-2013, 07:03 AM
Thank you very much for your help

mancubus
09-09-2013, 07:36 AM
Range("E20").End(xlDown).Offset(1) = Application.Sum(Range("E20:E" & Range("E20").End(xlDown).Row))

parscon
09-09-2013, 08:00 AM
Your code does not work.
Thankyou

Aussiebear
09-09-2013, 08:50 PM
Your code does not work.
Thankyou

Which code does not work? Please be more descriptive when replying to others who are trying to assist you.

mancubus
09-09-2013, 10:51 PM
Your code does not work.Thankyouyes it does.
Sub col_tot() Range("E20").End(xlDown).Offset(1) = Application.Sum(Range("E20:E" & Range("E20").End(xlDown).Row))End Sub