Log In
Register
Home
New Member
September 4, 2020
Offline
Here is what I need the macro to do.
See Image 1 first:
Essentially what the data is showing how many loan accounts will there be over time. Column A is the months with each row equaling a subset of customers that we gain in that period.
Column A:BI (60 months) is also months but its showing how the number of accounts is decreasing over time. We have a decreasing % lookup table that is shown in image 3. So 1st month there will be 3.2 accounts, 2nd month there will be 3.2*BL2 accounts which equals 2.7.
In month 2 (C257 of image 1) we expect 9.7 new accounts (different pool accounts then row 256) then in month we are multiplying the 9.7 by BL2 again. Then BM2, then BN2 and so on.
The macro needs to move 1 column to the right and multiply the 3.2 (B256) by BL2. Then move 1 column to the right and multiply 3.2 (B256) by BM2 and so on. It then needs to return to B256, move one row down, move one column to the right, lookup the number of accounts reference table (Image 2) (9.7). Then move right and multiply C257 (9.7) by BL2. And repeat that process for all 60 months. Thank you for any help you could provide!
October 5, 2010
Offline
Hi Scott,
Please supply a workbook with data, we can't write any code to affect that data without it.
Regards
Phil
New Member
September 4, 2020
Offline
Sorry about that. The workbook is attached.
October 5, 2010
Offline
Hi Scott,
the workbook doesn't quite match up with the pictures or your description,so it's a little tricky to follow and I don't want to write code for cells/ranges that may very well change. I can't test if the code is correct either.
That said, to do what you want it would be just as quick to do it manually for only 60 cells.
In rows 64:68 I've entered some formulae. I think this is what you are trying to do but the values you are multiplying by start in E2 not BL2.
The values in image 1-1 don't agree with my calculations either. For example in Months 3 and 4 the first values in the row in your image are 9.7 but the lookup tables shows 19.3?
Regards
Phil
1 Guest(s)
