sum by rows across columns

[jeansb]

Can anyone help me with my code as follow:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("sheet1").Activate
Dim de As Range
Dim i, j As Integer
For Each de In Range("e1:e1000")
For i = 1 To 1000
For j = 1 To 4
de = Application.WorksheetFunction.Sum(Range(Cells(1, j), Cells(i, 1)))
Next j
Next i
Next de
End Sub

I have 5 columns, the fifth column is the total column and 1000 rows
this code does not give the correct total results in column (e1:e1000)
I think there are quite few solutions for this and it is very easy, but i can't find the correct answer.
Thanks for your inputs

 

[Tom Urtis]

Well, a few things...

If this code is going into the Sheet1 module, there is no need to include an Activate line.

I cannot understand why you'd want to recalculate 1000 formulas with the mere selection of any and every cell, especially when you only need to enter the formulas once and leave them there, even if by code, instead of the static value approach you took for the WorksheetFunction Sum.

However, the short answer that comes closest to what you have, and what you are looking for, might be this, but it's an inefficient design premise, if indeed you are summing A:D for rows 1 to 1000 on every cell selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
.ScreenUpdating = False
.EnableEvents = False
With Range("E1:E1000")
.Formula = "=SUM(RC1:RC4)"
.Value = .Value
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[jindon]

Hi,

not sure if i understand what you want to do
if you have col.E to total it's row, like
=SUM(A1:E1) then

For Each de in Range("e1:e1000")
de.FormuaR1C1="=sum(rc1:rc[-1])"
next

Or

Range("e1").FormulaR1C1="=sum(rc1:rc[-1])"
Range("e1").AutoFill Destination:=Range("e1:e1000")

hope this helps
jindon

 

[jeansb]

thank you all for your quick reply .
your solution is work great !!

 

[jeansb]

How do I add a condition if sum=0 then it will show "" in total colum e ?

 

[jindon]

Hi

Range("e1").FormulaR1C1 = "=if(sum(rc1:rc[-1])=0,"""",sum(rc1:rc[-1]))"

rgds,

jindon