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

	AC FP Equity	FP FP Equity	MC FP Equity	GLE FP Equity
Date	PX_LAST	PX_LAST	PX_LAST	PX_LAST
	35,88	44,27	155,45	155,45
06/09/2016	35,785	44,095	156,15	156,15
07/09/2016	35,98	44,605	156,45	156,45
08/09/2016	36,145	44,445	154,1	154,1
09/09/2016	35,805	43,855	152,2	152,2
12/09/2016	34,835	43,435	152	152
03/10/2017	42,11	45,62	234,9	234,9

<tbody>
</tbody>

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:

Profitability				Scenarii					Profitability 2
-0,0026513	-0,00396	0,004493	0,004493		234,278	233,9714	235,9578	235,9578		-0,00066	-0,00099	0,001123	0,0011232	sum
0,00543441	0,0115	0,001919	0,001919		236,18	237,6168	235,3513	235,3513		0,001359	0,002875	0,00048	0,0004799	sum
0,00457542	-0,00359	-0,01513	-0,01513		235,9772	234,0574	231,3716	231,3716		0,001144	-0,0009	-0,00378	-0,003784	sum
-0,0094511	-0,01336	-0,01241	-0,01241		232,6904	231,7817	232,0038	232,0038		-0,00236	-0,00334	-0,0031	-0,003102	sum
-0,027465	-0,00962	-0,00131	-0,00131		228,5363	232,6504	234,5913	234,5913		-0,00687	-0,00241	-0,00033	-0,000329	sum
0,18966265	0,049081	0,435279	0,435279		283,9569	246,7166	363,0132	363,0132		0,047416	0,01227	0,10882	0,1088198	sum

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

 

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