Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 557
Default CORRECTION NEEDED IN MACRO

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CORRECTION NEEDED K[_2_] Excel Programming 2 January 22nd 08 12:54 PM
NEED CORRECTION IN MACRO K[_2_] Excel Programming 2 December 17th 07 12:50 PM
correction needed K[_2_] Excel Programming 5 December 16th 07 08:32 PM
Excel Macro Question - correction [email protected] Excel Programming 1 April 10th 07 01:34 PM
Macro correction Meltad Excel Programming 2 October 9th 06 12:15 PM


All times are GMT +1. The time now is 07:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"