Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distribution of production to different production sites
Hi everyone,
again I have a question about programming a button in Excel 2003: Let's say the production of a product increases in Dec.09 (Cell: G7) by 1000 pieces (Cell: G13). I have to 2 production sites which can produce the product. Now I want to implement a button where I can enter a distribution of the 1000 pieces, for example 70% to site 1 and 30% to site 2. The macro should enter the number of pieces for site 1 (=700) to Cell G15 and 300 for site 2 to Cell G16 . But there is an additional constraint: Each site has a maximum capacity which cannot be exceded. Let's say site 1 has a capacity of 600 (Cell:G1) and site 2 a capacity of 500 (Cell:G2). The macro should now distribute 600 pieces (=max. capacity) to site 1 (-60%) and tag the cell G15 with a red background because site 1 has reached its maximum capacity. The macro should distribute the rest (40%=400) to site 2 (G16). If each site reaches its maximum capacity the cells G15 & G16 should be tagged red. Q1: Which button should I use (the distribution should be variable each month)? Q2: Has someone an idea how to program the macro? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distribution of production to different production sites
This should be the code Sub test() Site1Percentage = 0.7 Site1Percentage = 0.3 Site1Max = Range("G1") Site2Max = Range("G2") ProductionChange = Range("G7") If ProductionChange Site1Max + Site2Max Then Site1Increase = Site1Max Site2Increase = Site2Max Else Site1Increase = ProductionChange * Site1Percentage If Site1Increase = Site1Max Then Site1Increase = Site1Max End If Site2Increase = ProductionChange - Site1Increase If Site2Increase = Site2Max Then Site2Increase = Site2Max Site1Increase = ProductionChange - Site2Increase End If End If Range("G15") = Site1Increase Range("G16") = Site2Increase If Site1Increase = Site1Max Then Range("G15").Interior.ColorIndex = 3 End If If Site2Increase = Site2Max Then Range("G16").Interior.ColorIndex = 3 End If End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151285 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Production/Inventory | New Users to Excel | |||
Production Yields | New Users to Excel | |||
forecast production | Excel Worksheet Functions | |||
Production CLock | Excel Worksheet Functions | |||
Summary of Production | Excel Discussion (Misc queries) |