Need help with macro and for loop

Hello all,

I need help with writing code in VBA.

I want to do a calculation for multiple years where te end value of year 2021 is the start value of year 2022.

Also I want to save the sum of one specific column for all the years and for example 5% of this sum of this column in 2021 will be used to increase the starting value in 2022.

I already tried some VBA code and made a similar, but simple version of my problem. (see attachment).

Thank you in advance!

Because the original problem is quite large and includes more some increasing variabeles per age, like salary.

The original problem must represent a pension fund, where the column old is the value of your pension at t,

then the column new: you add some percentage of your salary to your pension, and column end is the value at the end of the year.

Next year, you start with the value of the end of the previous year.

To make it more difficult: the total value at the start of the year 2021, will be invested in some stock or bonds and if the return is positive, then a percentage of this return will be add to your stariting value in 2022.

I'm sorry, I will try but most of the variable names are Dutch. In the file I made the important sheets red.

So we start with the employee tab. For now this is a list of 100 fake persons, but I would like this macro to work for any number of employees.

A person consist of a Name(naam), Date of Birth(Geboortedatum), sex(geslacht, 1 is Male, 2 is Female), Salary(Salaris), FTE(full time percentage) and value of the pension t = 0 (aanspraak).

Then to the calculation tab and top left we see a counter(teller) and the start year (2021). For every person at year t, we start with a pension value at t=0, then a part of your salary will be added to your start value(column K) and then the value at the end of the year is column O. This column O for the year 2021 is equal to the starting value in 2022.

The last 3 columns (Q, R, S) calculate the present value of the Pension.

In the pensionfund tab we start in 2021 with the liabilities of the fund, cel C5, then there are some costs but these are not important. We assume at the start that the assets of the pensionfund are equal to the liabilities at the start. This we invest and we get an return Assets and Bonds for 2021. At the end of the year we have the total value of the Asset. For the coverage ratio(dekkingsgraad) we divide these two and if it is larger than 100%, the pension are increased the next year. So C24 in the calculation for the year 2021 is used to set the level of indexation in column G in calculation tab in year 2022 and so on.

My problem now is that yellow column in year t, must be the green column in year t + 1.

And the orange column in year t+1 depends on !pensionfund C24 of year t

I already tried some work in the macro tab, but I did not succeed

The VBA code I wrote can be activated by the commandbutton in the first tab.

I hope it is clear

Many thanks in advance

kind regards

Quote from Carim

Hello Frank,

Thanks a lot for all your efforts ... to explain ...

If my understanding is correct ...In the Sheet Calculation ... in the cell G2 .... you could test following formula

Code

=INDEX('Pensionfund '!$24:$24,MATCH(Teller,'Pensionfund '!$15:$15,0))*Calculation!F2

Let me have your feedback ...

Display More

Thanks!

It works with Match(Teller-1, ....)

Do you also know how the output of your macro in year t, can be used as input for you macro in year t + 1.

Now the input for the Macro is a matrix which refers to the calculation tap.

If write in the VBA code:

if Teller> start Then

input =output

Else

input = starting values

end if

But the starting values refers to cells in the calculation tab. Now if I overwrite the Input with the output, these references disappear.

Teller is the counter for number of years

[inputrij].Copy: Select the input matrix

[start_output]: Storage of the output of the maco

Firstly, it's my first time writing VBA code, so I did not know that Select and Selection only slows down the process.

Secondly, I included Teller because I want to be able to use the output in 2033, for example in a graph

Each row represents an employee with a fictive starting value(column D). For these employees, we determine their age (column C).

If an employee is younger than 68, we determine their contribution for this year which depend on age and salary (Column F) and their benefit is 0 (column G)

If an employee is older than 68, thier contribution = 0 (column F) and we determine their benefit for this year(column G), which depend on their starting value.

Finally, in column H, we have the value at the start + their contribution - their benefits and this is equal to the value at the end of the year.

Then next year, the fictive starting value in column D is now equal to the value of the end of the previous year, and so on, Now the years are printed next to each other, but I would like that column H(year 2021) is column Q for year 2022. Then repeat the same steps.

Column J, K and L are not important for now. These columns are used to calculate the present value of the future cash flows, which is not necessary at the moment.

Yes, that is right

Thank you so much for your help!