Manual schedule file to automated using VBA

[Raja10]

Trying to create a scheduling chart(a bit like gnatt chart). I was able to work the code for one line but couldn't do it for the second line items to start from where it left over in first. I have 4 centers and each center needs its own schedule. to start from the column L. Column J has total fans to do in the batch. Column I is the center where theses fans are to be worked on. column H is the max output for the week for that center. column G has max output for day, and column F has output/hour.
L2 is week 1, M2 is week 2 and so on.

For example,
for row 3,
L3 is 110 because (J3-sum(K3:K3)>=H3) so they can produce 110 fans that week.
M3 is 110 because (J3-sum(K3:N3)>=H3) so they can produce 110 fans that week.
but N3 is only 10 because (J3-sum(K3:M3)<H3) so the cell value is (J3-sum(K3:M3))

for row 4,

L4 and M4 are blank because L3 and M3 satisfed the total work for the weeks
and N4 is 68 becasue N3<>H3 so there is room to produce more fans that week, but we have used some time to produce which is N3/F3. total hours in a week is 40 so the value is 40-( N3/F3 )*F4 which is 68.


for row 5,

It has to go tot last active column in the above row which is O4
if O4 = H4 then move to P5
check all the above rows in the column O
so value O5 = rounddown((40-((O4/F4)+(O3/F3)))*F5


for row 7,

check last valued column in the above row which is P6
but P7 = blank because ((40-((O5/F5)+(O4/F4)+(O3/F3)))*F6 = 0)
move to P8 and do the same


This has to be repeated for all the centers

This is the code I have written but it only give the first row right. and the second row starts from the beginning instead of where the first row values end. Also does not calculate the hours used by previous rows.

Please Login or Register  to view this content.

Please see the attached excel file and let me know if you have any questions. I appreciate any help. thank you in advance.