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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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
 
Upvote 0
Hi

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

rgds,

jindon
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top