| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
PLEASE CAN ANYBODY HELP - MACRO NEEDED
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. Mike F "K" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
PLEASE CAN ANYBODY HELP - MACRO NEEDED
On Apr 25, 11:54*am, "Mike Fogleman" wrote:
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. Mike F"K" wrote in message ... 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- Hide quoted text - - Show quoted text - Hi Mike, Thanks for replying. yes that will be good idea. Please do reply with the code |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
PLEASE CAN ANYBODY HELP - MACRO NEEDED
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:D" & 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:D" & 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 "K" wrote in message ... On Apr 25, 11:54 am, "Mike Fogleman" wrote: 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. Mike F"K" wrote in message ... 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- Hide quoted text - - Show quoted text - Hi Mike, Thanks for replying. yes that will be good idea. Please do reply with the code |
| Reply |
| 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) | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
