Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP
macro below works perfectly fine as it get percentages of Sheet1
column E amounts and other data in same row to Sheet2 Sub test() Dim LastRowColD As Long Dim i, j, k As Long Dim A_Percents(1 To 12) As Integer A_Percents(1) = 8 A_Percents(2) = 8 A_Percents(3) = 9 A_Percents(4) = 8 A_Percents(5) = 8 A_Percents(6) = 9 A_Percents(7) = 8 A_Percents(8) = 8 A_Percents(9) = 9 A_Percents(10) = 8 A_Percents(11) = 8 A_Percents(12) = 9 LastRowColD = Sheets(1).Range("D1").End(xlDown).Row k = 2 For i = 2 To LastRowColD If Cells(i, 4).Value = "A" Then For j = 1 To UBound(A_Percents) Sheets(2).Cells(k, 1).Value = Sheets(1).Cells(i, 1).Value Sheets(2).Cells(k, 2).Value = Sheets(1).Cells(i, 2).Value Sheets(2).Cells(k, 3).Value = Sheets(1).Cells(i, 3).Value Sheets(2).Cells(k, 4).Value = "A" Sheets(2).Cells(k, 5).Value = (A_Percents(j) / 100) * Sheets(1).Cells(i, 5).Value Sheets(2).Cells(k, 6).Value = Sheets(1).Cells(i, 6).Value k = k + 1 Next End If Next Sheets(2).Cells(1, 1).Value = "AC" Sheets(2).Cells(1, 2).Value = "CO" Sheets(2).Cells(1, 3).Value = "FO" Sheets(2).Cells(1, 4).Value = "CODE" Sheets(2).Cells(1, 5).Value = "AMT" Sheets(2).Cells(1, 6).Value = "DETAIL" End Sub I want macro to put period code which will be 200801,200802 etc with each percentage of amount in column 7 of Sheet2. by doing this it will tell that how much percentage figure will be there in specific period. So I tried doing some changes in above macro (see macro below) but for some reason its not working. Please anybody can tell that what i am doing wrong and can suggest any thing. I want periods to be appear in column 7 of Sheet2. for example if amount of 1000 have divided by percentage given in macro then result should be something like this (see below). (Note:- i am just showing column E and G result below) E G --- col 80 200801 80 200802 90 200803 80 200804 80 200805 90 200806 80 200807 80 200808 90 200809 80 200810 80 200811 90 200812 (CHANGED MACRO) Sub test() Dim LastRowColD As Long Dim i, J, k, t As Long Dim A_Percents(1 To 12) As Integer Dim MTH(1 To 12) As Integer MTH(1) = 200801 MTH(2) = 200802 MTH(3) = 200803 MTH(4) = 200804 MTH(5) = 200805 MTH(6) = 200806 MTH(7) = 200807 MTH(8) = 200808 MTH(9) = 200809 MTH(10) = 200810 MTH(11) = 200811 MTH(12) = 200812 A_Percents(1) = 8 A_Percents(2) = 8 A_Percents(3) = 9 A_Percents(4) = 8 A_Percents(5) = 8 A_Percents(6) = 9 A_Percents(7) = 8 A_Percents(8) = 8 A_Percents(9) = 9 A_Percents(10) = 8 A_Percents(11) = 8 A_Percents(12) = 9 LastRowColD = Sheets(1).Range("D1").End(xlDown).Row k = 2 For i = 2 To LastRowColD If Cells(i, 4).Value = "A" Then For J = 1 To UBound(A_Percents) For t = 1 To UBound(MTH) Sheets(2).Cells(k, 1).Value = Sheets(1).Cells(i, 1).Value Sheets(2).Cells(k, 2).Value = Sheets(1).Cells(i, 2).Value Sheets(2).Cells(k, 3).Value = Sheets(1).Cells(i, 3).Value Sheets(2).Cells(k, 4).Value = "A" Sheets(2).Cells(k, 5).Value = (A_Percents(J) / 100) * Sheets(1).Cells(i, 5).Value Sheets(2).Cells(k, 6).Value = Sheets(1).Cells(i, 6).Value Sheets(2).Cells(k, 7).Value = MTH(t) k = k + 1 Next End If Next Sheets(2).Cells(1, 1).Value = "AC" Sheets(2).Cells(1, 2).Value = "CO" Sheets(2).Cells(1, 3).Value = "FO" Sheets(2).Cells(1, 4).Value = "CODE" Sheets(2).Cells(1, 5).Value = "AMT" Sheets(2).Cells(1, 6).Value = "DETAIL" Sheets(2).Cells(1, 7).Value = "PERIOD" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|