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

<tbody>

</tbody>

The result is this:

<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,

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>

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:

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