populate cells based on a start year and cycle

[Seattlites]

Please see attached file. I'm trying to take the cost from Column D to populate across columns H:AU based on the CYCLE YEARS and DUE IN YEAR listed in Columns E and F. Your help greatly appreciated.


Regards,
Seattlite

[Mumps1]

Try:

Please Login or Register  to view this content.

[Seattlites]

Thank you Mumps 1. The code worked, but it looks like it's designed only for Row 6 and I'm looking for a solution to populate data for any row within the UninflatedComponentCosts arrary. Any additional thoughts on how to accomplish that?

[Mumps1]

Will each row have the same CYCLE YEARS and DUE IN YEAR? For example, row 6 has 5 and 4 respectively for CYCLE YEARS and DUE IN YEAR. Will all the rows below also have 5 and 4?

[Seattlites]

Will each row have the same CYCLE YEARS and DUE IN YEAR? For example, row 6 has 5 and 4 respectively for CYCLE YEARS and DUE IN YEAR. Will all the rows below also have 5 and 4?

Each row will have a unique COST, CYCLE YEARS and DUE IN YEAR, so the solution will need to evaluate columns D, E and F for each row to populate the array appropriately.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Seattlites]

Thanks again Mumps1. This time it appears that the first two rows are properly populated, but nothing beyond that. I've uploaded the macro-enabled workbook.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Seattlites]

Thank you! I ran it once and it worked!

I added a random row further down the sheet and ran it again which forced me to kill the Excel process on my computer because it was stuck in an endless loop. Not sure what caused that...? Is there a way to get it to stop trying when it reaches what should be the end of the array area (which could have more rows inserted)?

[Mumps1]

The macro is designed to stop when it reaches the last Cost Value in column D. Are there any blank cells between the first Cost Value in column D and the last Cost Value in column D?

[Seattlites]

The macro is designed to stop when it reaches the last Cost Value in column D. Are there any blank cells between the first Cost Value in column D and the last Cost Value in column D?

Ah I see. I did randomly populate a few rows and left some blank between the first set of values in D/E/F. That's what caused the "endless loop." Admittedly, the worksheet is not designed to be used that way. I'll add some instructions not to leave any rows blank. Great solution. Thanks again!

[Mumps1]

This version will ignore the blank cells.

Please Login or Register  to view this content.

[Seattlites]

Ah perfect! A very elegant solution.

[Mumps1]

Glad to help.

[Seattlites]

Hello again Mumps1. I previously neglected to add the individual component cost summaries to the right of the UninflatedComponentCosts arrary in columns AW and AX (see updated attached file). When I added these this and then ran the macro, the macro wiped out the formulas in these two new columns. Is there a way to limit the macro to stop at column AU?

[Mumps1]

Try:

Please Login or Register  to view this content.

[Seattlites]

Thanks again Mumps1. I seem to have still encountered an overwrite scenario where the macro looks beyond column AU (the end of the array for disseminating values) in certain situations. Any thoughts? Example attached with cells highlighted in red where values were written beyond the end of the UninflatedComponentCosts arrary.

[Mumps1]

Hopefully, this will solve the problem. I didn't realize that you would be adding columns of data to the right.

Please Login or Register  to view this content.

[Seattlites]

My bad. I added a new column before the array and it threw everything off. I decided to just remove it and used your new code. Everything's great now. Thanks again

[Mumps1]

My pleasure.