sum by rows across columns

jeansb

New Member
Joined
Jul 20, 2003
Messages
19
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Jul 20, 2003
Messages
19
thank you all for your quick reply .
your solution is work great !! :biggrin:
 

jeansb

New Member
Joined
Jul 20, 2003
Messages
19
How do I add a condition if sum=0 then it will show "" in total colum e ?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

rgds,

jindon
 

Forum statistics

Threads
1,147,508
Messages
5,741,572
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top