Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE CAN ANYBODY HELP - MACRO NEEDED
Hi, I have percentages table in Sheet1 (see below)
ROW A B ---col 1 GX GT ---codes 2 % % 3 8 0 4 8 25 5 9 0 6 8 0 7 8 25 8 9 0 9 8 0 10 8 25 11 9 0 12 8 0 13 8 25 14 9 0 I have another table in Shee2 (see below) in which column B I have amounts and in column A I'll put codes. ROW A B 1 CODE AMOUNT---headings 2 GX 2630 3 GT 2170 I want macro that when I put any code in column A of Sheet2 then it should multiply the amount figure of same row cell in column B whith the percentages given in Sheet1 under the exact code and produce result in Sheet3 and also the figures it will put on Sheet3 should be rounded. In Sheet3 macro should produce result something like this (see below) A B----col CODE AMOUNT GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 but macro should also round the figures so result should be then (see below) A B----col CODE AMOUNT GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 The only problem by rounding will be the amounts different. If you total GX code figures on Sheet3 after rounding it will be 2628 but actual figure is 2630 on Sheet2 and same with GT code the total will be 2172 but actual figure on Sheet2 is 2170. by rounding figures it will not give the same figures so i want macro to when it will produce result on Sheet3 with the rounded figures then it should check the difference of actual amount of Sheet2 column B and the total of Sheet3 rounded figures under the same code and what ever the difference come like in above table its giving difference 0f 2 then it should take that away on put in on the last percentage figures on Sheet3. So the result should be then A B----col CODE AMOUNT GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 239 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 541 GT 0 if you see that the last figures of each code been changed by amount of 2. 237 to 239 and 543 to 541 I hope I explained what I am trying to say. Can anybody help please for more explaintion i have uploaded the excel file see the link below http://www.savefile.com/files/1521549 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to set macro security in Excel to minimum | Excel Programming | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |