| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP - HOW TO SPLIT AMOUNT BY MACRO
Hi,
I have data in "Sheet1" and sheet name is "DATA" and in column "D" I'll put codes like "A , B , C" and in column "E" I have amounts (see below) A B C D E F G --- col AC CC FO CODE AMT DETAIL REF --- headings 333 GS1 XX12 A 1000 IN LIST Z8000T 667 H23 RRT3 B 2000 NOT IN Y9000X 444 J55 GHJ9 C 3000 REFERE H5633U I want macro to split amount of column "E" when I put any code (A , B or C) in column "D". The percentage of codes are below. A B C ---- col A B C -----code headings 8 10 0 ---percentages 8 10 0 9 10 0 8 10 0 8 10 50 9 10 0 8 10 0 8 10 0 9 10 0 8 10 50 8 0 0 9 0 0 Macro should throw all result in "Sheet2" and "Sheet2" name is "Result". For example if I put code "A" in cell "D2" and amount 1000 in "E2" then macro should got through the percentage of code "A" as shown above and then start spliting 1000 like (8/100*1000) then in cell below (8/100*1000) ans so on. And when if put code "B" in cell "D3" and amount 2000 against the code then macro should go through code "B" percentages and put all split below the previous split in "Sheet2" Macro should produce result some thing like this in "Sheet2" or in "Result" sheet. (See below) A B C D E F G ----col AC CC FO COD AMT DETAIL REF ---headings 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 90 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 90 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 90 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 80 IN LIST Z8000T 333 GS1 XX12 A 90 IN LIST Z8000T 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 200 NOT IN Y9000X 667 H23 RRT3 B 0 NOT IN Y9000X 667 H23 RRT3 B 0 NOT IN Y9000X 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 1500 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U 444 J55 GHJ9 C 1500 REFERE H5633U 444 J55 GHJ9 C 0 REFERE H5633U |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
PLEASE HELP - HOW TO SPLIT AMOUNT BY MACRO
Hi
I doubt you will get a reply to this - it is (probably) a lot of work to both clarify and code, and not really in the spirit of asking for freely given help. You will probably get help if you break the problem down into small pieces and ask for help on each piece. regards Paul On Apr 16, 9:17*am, K wrote: Hi, I have data in "Sheet1" and sheet name is "DATA" and in column "D" I'll put codes like "A , B , C" and in column "E" I have amounts (see below) *A * * * * B * * * * *C * * * * D * * * * E * * * * * *F * * * * * * G --- col AC * * *CC * * * FO * * CODE * AMT * * DETAIL * * * * REF --- headings 333 * * GS1 * * XX12 * * *A * * * 1000 * * IN LIST * * * Z8000T 667 * * H23 * * RRT3 * * *B * * * 2000 * * NOT IN * * * Y9000X 444 * * J55 * * *GHJ9 * * *C * * * 3000 * * REFERE * *H5633U I want macro to split amount of column "E" when I put any code (A , B or C) in column "D". *The percentage of codes are below. A * * B * * C *---- col A * * B * * C *-----code headings 8 * * 10 * * 0 ---percentages 8 * * 10 * * 0 9 * * 10 * * 0 8 * * 10 * * 0 8 * * 10 * * 50 9 * * 10 * * 0 8 * * 10 * * 0 8 * * 10 * * 0 9 * * 10 * * 0 8 * * 10 * * 50 8 * * 0 * * * 0 9 * * 0 * * * 0 Macro should throw all result in "Sheet2" and "Sheet2" name is "Result". For example if I put code "A" in cell "D2" and amount 1000 in "E2" then macro should got through the percentage of code "A" as shown above and then start spliting 1000 like (8/100*1000) then in cell below (8/100*1000) ans so on. And when if put code "B" in cell "D3" and amount 2000 against the code *then macro should go through code "B" percentages and put all split below the previous split in "Sheet2" Macro should produce result some thing like this in "Sheet2" or in "Result" sheet. (See below) A * * * * *B * * * * * C * * * D * * *E * * * * * * * *F * * * * * * G ----col AC * * *CC * * * *FO * COD *AMT * * * *DETAIL * * * * REF ---headings 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 90 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 90 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 90 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 80 * * * * *IN LIST * * * *Z8000T 333 * * GS1 * * XX12 * * A * * 90 * * * * *IN LIST * * * *Z8000T 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 200 * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 0 * * * * * NOT IN * * * *Y9000X 667 * * H23 * * RRT3 * * B * * 0 * * * * * NOT IN * * * *Y9000X 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 1500 * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 1500 * * REFERE * * H5633U 444 * * J55 * * *GHJ9 * * C * * 0 * * * * * REFERE * * H5633U |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I split a sheet so I can have different amount of columns? | Excel Worksheet Functions | |||
| PLEASE HELP ! "SPLIT AMOUNT BY MACRO" | Excel Programming | |||
| SPLIT AMOUNT BY MACRO | Excel Programming | |||
| How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
| split a cash amount into two separate cash amounts | Excel Programming | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
