Loop Calc Excel Forum Example updated.xlsx

I'm try to create a pro forma based on a rent schedule as you can see in the example. What I'd like to do is to loop through all the tenants and calculate the rent based on the schedule given in the columns from A:I

I was able to write code to get to what's in columns K:X

For example, for 2013 it

My thought was to separate out the dates within the code, so I have the year as one variable and the month as another. Using these variables I'd see how much time in each year was left at that rate and come up with a rate for each year. Once the scheduled changes in columns D:E finish it uses the rate in column I.

The attempt at is below. I know it's a disaster and don't know if it helps to provide it other that to show I've been trying my best at it. I ran into trouble with having multiple rent changes in the same year. The other big issues was keeping track of what was happening with all the loops and offsets. There's got to be a better and easier to follow way to to do it.

If anyone could provide any help I would greatly appreciate it.

Please Login or Register  to view this content.