Macro to fill in cells based on a single value

[MaxTiger]

Dear Reader,

I have an excel problem of which I hope some of you could help me with. For the company I work at, I want to extend the current master production schedule (in excel) with hypothetical values. For instance, the amount of production starts for the coming 6 months is defined (customer orders), but for capacity planning purposes, I want to see what happens if I extend this.

Example: Production starts (1 start = 1 product)

Production starts January = 5
Production starts February = 7
Production starts March = 4
Production starts April = 8
Production starts May = 7
Production starts June = 6

The coming 6 months, production output is 37. I want excel to fill in the production starts for the next 6 months based on a target of let’s say 48 (or 57) products, evenly distributed over these 6 months. Logically, only integer values are allowed. This would result in

Production starts July = 8 (9.5 → 10)
Production starts Augustus = 8 (9.5 → 9)
Production starts September = 8 (9.5 → 10)
Production starts October = 8 (9.5 → 9)
Production starts November = 8 (9.5 → 10)
Production starts December = 8 (9.5 → 9)

So the idea is to put the desired output in a certain cell, and the macro fills in the values for the coming period based on that output. I was wondering how I could make this into a macro, as manually inserting this for a long term period (>5 years) would mean a lot of work each time I want to revaluate a given production output. Note: The production starts here are displayed vertically, in my current worksheet this is horizontally.

I hope some of you can help me, thank you in advance!

 

[ravishankar]

if A1 has 48 or 57 B1=D1=F1=roundup($A$1/6,0) and C1=E1=G1=rounddown($A$1/6,0)
ravishankar