PLEASE CAN ANYBODY HELP - MACRO NEEDED

[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

for more explaintion i have uploaded the excel file see the link
below
http://www.savefile.com/files/1521549

 

[K]

I don't have time to work on the code right now, but in sheet3 would it be
better to format the numbers with no decimal places? That way 236.7 will
display as 237 but the actual amount of 236.7 is still in the cell for
accurate summing. Eliminate the extra comparison code.















- Show quoted text -

Hi Mike, Thanks for replying. yes that will be good idea. Please do
reply with the code

 

[Mike Fogleman]

I have downloaded your workbook, added the code and emailed to you.
The code is this:

Sub MultAmt()
Const x = 12
Dim i As Integer
Dim LRow As Long
Dim rng As Range, c As Range
Dim rng2 As Range, c2 As Range

LRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheet2.Range("D2" & LRow)
LRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
If LRow = 1 Then
'do nothing
Else
Sheet3.Rows("2:" & LRow).Delete
End If
i = 1
For Each c In rng
LRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
c.EntireRow.Copy Sheet3.Range("A" & LRow + i & ":A" & LRow + x)
Next
LRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheet3.Range("D2" & LRow)
LRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Sheet1.Range("A3:M" & LRow)
i = 2
For Each c In rng
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i) / 100)
If i = 13 Then
i = 2
Else
i = i + 1
End If
Next
Sheet3.Columns("E:E").NumberFormat = "0"
End Sub


Mike F

I don't have time to work on the code right now, but in sheet3 would it be
better to format the numbers with no decimal places? That way 236.7 will
display as 237 but the actual amount of 236.7 is still in the cell for
accurate summing. Eliminate the extra comparison code.















- Show quoted text -

Hi Mike, Thanks for replying. yes that will be good idea. Please do
reply with the code