CORRECTION NEEDED IN MACRO

K

K

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
 

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