Summing rows for undefined columns number

[ineedmesome]

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).


[TABLE="width: 431"]
<tbody>[TR]
[TD][/TD]
[TD]AC FP Equity[/TD]
[TD]FP FP Equity[/TD]
[TD]MC FP Equity[/TD]
[TD]GLE FP Equity[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]PX_LAST[/TD]
[TD]PX_LAST[/TD]
[TD]PX_LAST[/TD]
[TD]PX_LAST[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]35,88[/TD]
[TD="align: right"]44,27[/TD]
[TD="align: right"]155,45[/TD]
[TD="align: right"]155,45[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2016[/TD]
[TD="align: right"]35,785[/TD]
[TD="align: right"]44,095[/TD]
[TD="align: right"]156,15[/TD]
[TD="align: right"]156,15[/TD]
[/TR]
[TR]
[TD="align: right"]07/09/2016[/TD]
[TD="align: right"]35,98[/TD]
[TD="align: right"]44,605[/TD]
[TD="align: right"]156,45[/TD]
[TD="align: right"]156,45[/TD]
[/TR]
[TR]
[TD="align: right"]08/09/2016[/TD]
[TD="align: right"]36,145[/TD]
[TD="align: right"]44,445[/TD]
[TD="align: right"]154,1[/TD]
[TD="align: right"]154,1[/TD]
[/TR]
[TR]
[TD="align: right"]09/09/2016[/TD]
[TD="align: right"]35,805[/TD]
[TD="align: right"]43,855[/TD]
[TD="align: right"]152,2[/TD]
[TD="align: right"]152,2[/TD]
[/TR]
[TR]
[TD="align: right"]12/09/2016[/TD]
[TD="align: right"]34,835[/TD]
[TD="align: right"]43,435[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]152[/TD]
[/TR]
[TR]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]42,11[/TD]
[TD="align: right"]45,62[/TD]
[TD="align: right"]234,9[/TD]
[TD="align: right"]234,9[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 988"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Profitability[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Scenarii[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Profitability 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-0,0026513[/TD]
[TD="align: right"]-0,00396[/TD]
[TD="align: right"]0,004493[/TD]
[TD="align: right"]0,004493[/TD]
[TD][/TD]
[TD="align: right"]234,278[/TD]
[TD="align: right"]233,9714[/TD]
[TD="align: right"]235,9578[/TD]
[TD="align: right"]235,9578[/TD]
[TD][/TD]
[TD="align: right"]-0,00066[/TD]
[TD="align: right"]-0,00099[/TD]
[TD="align: right"]0,001123[/TD]
[TD="align: right"]0,0011232[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD="align: right"]0,00543441[/TD]
[TD="align: right"]0,0115[/TD]
[TD="align: right"]0,001919[/TD]
[TD="align: right"]0,001919[/TD]
[TD][/TD]
[TD="align: right"]236,18[/TD]
[TD="align: right"]237,6168[/TD]
[TD="align: right"]235,3513[/TD]
[TD="align: right"]235,3513[/TD]
[TD][/TD]
[TD="align: right"]0,001359[/TD]
[TD="align: right"]0,002875[/TD]
[TD="align: right"]0,00048[/TD]
[TD="align: right"]0,0004799[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD="align: right"]0,00457542[/TD]
[TD="align: right"]-0,00359[/TD]
[TD="align: right"]-0,01513[/TD]
[TD="align: right"]-0,01513[/TD]
[TD][/TD]
[TD="align: right"]235,9772[/TD]
[TD="align: right"]234,0574[/TD]
[TD="align: right"]231,3716[/TD]
[TD="align: right"]231,3716[/TD]
[TD][/TD]
[TD="align: right"]0,001144[/TD]
[TD="align: right"]-0,0009[/TD]
[TD="align: right"]-0,00378[/TD]
[TD="align: right"]-0,003784[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD="align: right"]-0,0094511[/TD]
[TD="align: right"]-0,01336[/TD]
[TD="align: right"]-0,01241[/TD]
[TD="align: right"]-0,01241[/TD]
[TD][/TD]
[TD="align: right"]232,6904[/TD]
[TD="align: right"]231,7817[/TD]
[TD="align: right"]232,0038[/TD]
[TD="align: right"]232,0038[/TD]
[TD][/TD]
[TD="align: right"]-0,00236[/TD]
[TD="align: right"]-0,00334[/TD]
[TD="align: right"]-0,0031[/TD]
[TD="align: right"]-0,003102[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD="align: right"]-0,027465[/TD]
[TD="align: right"]-0,00962[/TD]
[TD="align: right"]-0,00131[/TD]
[TD="align: right"]-0,00131[/TD]
[TD][/TD]
[TD="align: right"]228,5363[/TD]
[TD="align: right"]232,6504[/TD]
[TD="align: right"]234,5913[/TD]
[TD="align: right"]234,5913[/TD]
[TD][/TD]
[TD="align: right"]-0,00687[/TD]
[TD="align: right"]-0,00241[/TD]
[TD="align: right"]-0,00033[/TD]
[TD="align: right"]-0,000329[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD="align: right"]0,18966265[/TD]
[TD="align: right"]0,049081[/TD]
[TD="align: right"]0,435279[/TD]
[TD="align: right"]0,435279[/TD]
[TD][/TD]
[TD="align: right"]283,9569[/TD]
[TD="align: right"]246,7166[/TD]
[TD="align: right"]363,0132[/TD]
[TD="align: right"]363,0132[/TD]
[TD][/TD]
[TD="align: right"]0,047416[/TD]
[TD="align: right"]0,01227[/TD]
[TD="align: right"]0,10882[/TD]
[TD="align: right"]0,1088198[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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).

 

[Joe4]

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.