Summing rows for undefined columns number

Status
Not open for further replies.

ineedmesome

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

In a school project we have process this data in different ways. the point is that the user can enter a lot of equities data and the program will do all the computing. Now i'm in a step where the VBA macro has to compute the sum of each row of my last range (Profitability 2).


AC FP EquityFP FP EquityMC FP EquityGLE FP Equity
DatePX_LASTPX_LASTPX_LASTPX_LAST
35,8844,27155,45155,45
06/09/201635,78544,095156,15156,15
07/09/201635,9844,605156,45156,45
08/09/201636,14544,445154,1154,1
09/09/201635,80543,855152,2152,2
12/09/201634,83543,435152152
03/10/201742,1145,62234,9234,9

<tbody>
</tbody>

Code:
Sub rentabilite()

    'Déclaration des variables
    
    Dim cours As Single
    Dim cours_precedent As Single
    Dim renta As Single
    Dim renta2 As Single
    Dim i As Single
    Dim j As Single
    Dim nbcolumns As Single
    Dim nblines As Single
    Dim scenarii_column As Single


    'Initialisation des variables
    
    nbcolumns = Cells(2, Columns.Count).End(xlToLeft).Column
    nblines = Cells(Rows.Count, 2).End(xlUp).Row


    
    
    
    For j = 2 To nbcolumns
        For i = 4 To nblines


            cours = Cells(i, j).Value
            cours_precedent = Cells(i - 1, j).Value
            renta = WorksheetFunction.Ln(cours / cours_precedent)
            scenarii = Cells(nblines, nbcolumns).Value * (cours / cours_precedent)


            


            Cells(i, j + nbcolumns).Value = renta
            Cells(2, 2 + nbcolumns).Value = "Profitability"




            Cells(i, j + (nbcolumns * 2)).Value = scenarii
            Cells(2, 2 + (nbcolumns * 2)).Value = "Scenarii"
            
            renta2 = (1 / (nbcolumns - 1)) * WorksheetFunction.Ln(scenarii / Cells(nblines, nbcolumns))
            Cells(i, j + (nbcolumns * 3)).Value = renta2
            Cells(2, 2 + (nbcolumns * 3)).Value = "Profitability 2"
            Cells(i, (nbcolumns * 4) + 1).Value = "sum"


            
            
        Next i
    Next j
    
    
End Sub

The result is this:
ProfitabilityScenariiProfitability 2
-0,0026513-0,003960,0044930,004493234,278233,9714235,9578235,9578-0,00066-0,000990,0011230,0011232sum
0,005434410,01150,0019190,001919236,18237,6168235,3513235,35130,0013590,0028750,000480,0004799sum
0,00457542-0,00359-0,01513-0,01513235,9772234,0574231,3716231,37160,001144-0,0009-0,00378-0,003784sum
-0,0094511-0,01336-0,01241-0,01241232,6904231,7817232,0038232,0038-0,00236-0,00334-0,0031-0,003102sum
-0,027465-0,00962-0,00131-0,00131228,5363232,6504234,5913234,5913-0,00687-0,00241-0,00033-0,000329sum
0,189662650,0490810,4352790,435279283,9569246,7166363,0132363,01320,0474160,012270,108820,1088198sum

<tbody>
</tbody>

What i want is summing up each row of the range "Profitability 2", the range is undefined since it will depend on the number of equities the user will enter at first. Basically, in this specific set of data, i want to show in the column where, the sum of each row in "Profitability 2" range, for example U4 would be equal to Q4+R4+S4+T4

Thanks guys,


EDIT: Sorry for the unclearance... So basically, instead of having a column filled with "sum sum sum sum...", i want to have in the first cell of the column, the sum of the first row of all "Profitability 2" columns, so basically : (-0,00066 - 0,00099 + 0,001123 + 0,0011232).
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,936
Office Version
  1. 365
Platform
  1. Windows
Re-posted here: https://www.mrexcel.com/forum/excel...ells-each-row-selected-range.html#post4976908

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).


If you do not receive a response, you can "bump" it by replying to it again, though we advise you not to bump a thread more than once a day.

Since the other thread has a reply, I will close/lock this one.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,581
Messages
5,548,874
Members
410,881
Latest member
toonces
Top