Sum cells of each row in a selected range

ineedmesome

New Member
Joined
Dec 28, 2017
Messages
5
Hi guys,

I wanna sum the cells of each row in "Rentabilité 2" range and have the result in the column right next to it.

Here's the code i made so far, i managed to select the range under "Rentabilité 2", but don't know how to sum each row, do i need a for loop?

Code:
Sub sum()



    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim mc As Range
    
    With Worksheets("data").Cells
        Set mc = .Find("Rentabilité 2", LookIn:=xlValues).Offset(2)
            
        If Not mc Is Nothing Then
            lngLastRow = .Cells(Rows.Count, mc.Column).End(xlUp).Row
            lngLastCol = .Cells(mc.Row, Columns.Count).End(xlToLeft).Column
            .Range(mc, Cells(lngLastRow, lngLastCol)).Select
            range_renta2 = .Range(mc, Cells(lngLastRow, lngLastCol)).Select


        End If
        
    End With
    


End Sub



AC FP EquityFP FP EquityMC FP EquityGLE FP Equity
DatePX_LASTPX_LASTPX_LASTPX_LASTRentabilitéScenariiRentabilité 2
35,8844,27155,45
06/09/201635,78544,095156,1532,16-0,0026513-0,003960,004493-0,0197130,9827530,942231,2048930,45885-0,00066-0,000990,001123-0,004926
07/09/201635,9844,605156,4532,4050,005434410,01150,0019190,00758931,2342831,424331,1246931,301660,0013590,0028750,000480,0018973
08/09/201636,14544,445154,133,0650,00457542-0,00359-0,015130,02016331,2074630,9535730,5983831,697710,001144-0,0009-0,003780,0050407
09/09/201635,80543,855152,233,34-0,0094511-0,01336-0,012410,00828330,7727930,6526230,6819831,32337-0,00236-0,00334-0,00310,0020706
12/09/201634,83543,43515232,725-0,027465-0,00962-0,00131-0,0186230,2234130,7674931,0241830,49196-0,00687-0,00241-0,00033-0,004655
13/09/201634,74542150,832,14-0,002587-0,0336-0,00793-0,0180430,9847430,0386830,8197530,50968-0,00065-0,0084-0,00198-0,004509
14/09/201634,8741,49147,431,890,00359118-0,01222-0,0228-0,0078131,1767630,6877830,3645930,823360,000898-0,00305-0,0057-0,001952
15/09/201635,39541,51147,6532,0050,014943750,0004820,0016950,003631,5327231,0799731,1176931,177030,0037360,000120,0004240,0008999
16/09/201634,92540,605146,6531,13-0,0133677-0,02204-0,0068-0,0277230,652530,3877230,854630,2157-0,00334-0,00551-0,0017-0,00693
19/09/201635,37541,685149,0531,260,012802470,026250,0162330,00416731,4652631,8912631,573431,194730,0032010,0065630,0040580,0010418
20/09/201635,27541,46150,0531,065-0,0028308-0,005410,006687-0,0062630,9771930,8973231,2734230,87122-0,00071-0,001350,001672-0,001564
The result would be something like this :
Rentabilité 2
-0,00066-0,000990,001123-0,004926-0,0054561
0,0013590,0028750,000480,00189730,00661065
0,001144-0,0009-0,003780,00504070,00150245
-0,00236-0,00334-0,00310,0020706-0,0067346
-0,00687-0,00241-0,00033-0,004655-0,0142554
-0,00065-0,0084-0,00198-0,004509-0,0155367
0,000898-0,00305-0,0057-0,001952-0,0098099
0,0037360,000120,0004240,00089990,00518001
-0,00334-0,00551-0,0017-0,00693-0,0174817
0,0032010,0065630,0040580,00104180,01486325
-0,00071-0,001350,001672-0,001564-0,0019535

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col span="2"><col><col span="12"><col><col></colgroup><tbody>
</tbody>

Thanks in advance !
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,800
Office Version
  1. 365
Platform
  1. Windows
Here's one way you could do it, by using Excel's SUM function:

Code:
With Worksheets("data")
    Set mc = .Cells.Find("Rentabilité 2", LookIn:=xlValues).Offset(2)
    If Not mc Is Nothing Then
        lngLastRow = .Cells(Rows.Count, mc.Column).End(xlUp).Row
        lngLastCol = .Cells(mc.Row, Columns.Count).End(xlToLeft).Column
        With .Range(.Cells(mc.Row, lngLastCol + 1), .Cells(lngLastRow, lngLastCol + 1))
            .FormulaR1C1 = "=SUM(RC" & mc.Column & ":RC" & lngLastCol & ")"
            .Value = .Value 'optional, if you want values only, not formulae
        End With
    End If
End With
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,860
Messages
5,525,244
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top