MACRO TO WORKOUT PERCENTAGE

K

K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top