PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > CAN ANYBODY PLEASE HELP ME WITH THIS MACRO

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

CAN ANYBODY PLEASE HELP ME WITH THIS MACRO

 
 
K
Guest
Posts: n/a
 
      17th Apr 2008
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 With Quote
 
 
 
 
Mark Ivey
Guest
Posts: n/a
 
      18th Apr 2008
Without seeing the data on your sheet, I can only speculate at what might
work for you...

I found a few mistakes with your code by running it through debug mode...

Here are the fixes I would recommend.


Mark Ivey


Sub test2()
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 Long ' changed type due to value being a long
number


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
Next ' added a next statement for the second FOR loop
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 With Quote
 
K
Guest
Posts: n/a
 
      18th Apr 2008
On Apr 18, 2:43*am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> Without seeing the data on your sheet, I can only speculate at what might
> work for you...
>
> I found a few mistakes with your code by running it through debug mode...
>
> Here are the fixes I would recommend.
>
> Mark Ivey
>
> Sub test2()
> * * 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 Long * ' changed type due to value being a long
> number
>
> * * 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
> * * * * * * Next * *' added a next statement for the second FOR loop
> * * * * 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

Thanks for replying Mark. I been struggling to get this answer from
long time. Your macro work fine but it just sligtly giving different
result.
Please see the excel file in link below which I have uploaded on
www.savefile.com. I have explained everything in this file. hope
you'll understand what i am trying to say. Please do reply.

http://www.savefile.com/files/1511153
 
Reply With Quote
 
K
Guest
Posts: n/a
 
      18th Apr 2008
On Apr 18, 2:43*am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> Without seeing the data on your sheet, I can only speculate at what might
> work for you...
>
> I found a few mistakes with your code by running it through debug mode...
>
> Here are the fixes I would recommend.
>
> Mark Ivey
>
> Sub test2()
> * * 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 Long * ' changed type due to value being a long
> number
>
> * * 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
> * * * * * * Next * *' added a next statement for the second FOR loop
> * * * * 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

hi mark , thanks for replying i been struggling to get answer for this
question from long time now. your macro works fine but it giving bit
different result. please see my excel file in link below in which i
explained every thing. hope fully you can understand what i am tring
to say. please please do reply.
http://www.savefile.com/files/1511153
 
Reply With Quote
 
Mark Ivey
Guest
Posts: n/a
 
      18th Apr 2008
I will take a closer look at it this evening when I get home from work...

Mark Ivey

"K" <(E-Mail Removed)> wrote in message
news:3945aedd-8f47-46d5-972e-(E-Mail Removed)...
> On Apr 18, 2:43 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> Without seeing the data on your sheet, I can only speculate at what might
>> work for you...
>>
>> I found a few mistakes with your code by running it through debug mode...
>>
>> Here are the fixes I would recommend.
>>
>> Mark Ivey
>>
>> Sub test2()
>> 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 Long ' changed type due to value being a long
>> number
>>
>> 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
>> Next ' added a next statement for the second FOR loop
>> 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
>
> Thanks for replying Mark. I been struggling to get this answer from
> long time. Your macro work fine but it just sligtly giving different
> result.
> Please see the excel file in link below which I have uploaded on
> www.savefile.com. I have explained everything in this file. hope
> you'll understand what i am trying to say. Please do reply.
>
> http://www.savefile.com/files/1511153
 
Reply With Quote
 
Mark Ivey
Guest
Posts: n/a
 
      18th Apr 2008
I will see if I have some time at work today to take a look at it...

Otherwise it will be tonight before I can get back on it.

Mark Ivey

"K" <(E-Mail Removed)> wrote in message
news:40ed6872-1779-4497-a5bd-(E-Mail Removed)...
> On Apr 18, 2:43 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> Without seeing the data on your sheet, I can only speculate at what might
>> work for you...
>>
>> I found a few mistakes with your code by running it through debug mode...
>>
>> Here are the fixes I would recommend.
>>
>> Mark Ivey
>>
>> Sub test2()
>> 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 Long ' changed type due to value being a long
>> number
>>
>> 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
>> Next ' added a next statement for the second FOR loop
>> 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
>
> hi mark , thanks for replying i been struggling to get answer for this
> question from long time now. your macro works fine but it giving bit
> different result. please see my excel file in link below in which i
> explained every thing. hope fully you can understand what i am tring
> to say. please please do reply.
> http://www.savefile.com/files/1511153
>
 
Reply With Quote
 
Mark Ivey
Guest
Posts: n/a
 
      18th Apr 2008
K,

Just took a quick minute to review what you had....

Thanks for uploading the file. It really helped me better understand what
you were needing.


I think I found your problem. You had 2 FOR loops. One for the "A_Percents"
array, and one for the "MTH" array. Take note, the FOR loops were just used
to reference a specific INDEX point for each array. Since you want them
synchronized anyway you can do this job with just ONE FOR loop (as seen
below in the updated code). You can use the same INDEX position from one
array to do the same job in the other array.

What was happening in the last macro is that each array was being reference
12 times... In other words 12 times 12 equals 144 results.

I think the code below will get this issue fixed.

Mark Ivey



Sub test2()
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 Long ' changed type due to value being a long
Number


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)
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(J)
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 With Quote
 
K
Guest
Posts: n/a
 
      18th Apr 2008
On 18 Apr, 11:57, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> K,
>
> Just took a quick minute to review what you had....
>
> Thanks for uploading the file. It really helped me better understand what
> you were needing.
>
> I think I found your problem. You had 2 FOR loops. One for the "A_Percents"
> array, and one for the "MTH" array. Take note, the FOR loops were just used
> to reference a specific INDEX point for each array. Since you want them
> synchronized anyway you can do this job with just ONE FOR loop (as seen
> below in the updated code). You can use the same INDEX position from one
> array to do the same job in the other array.
>
> What was happening in the last macro is that each array was being reference
> 12 times... In other words 12 times 12 equals 144 results.
>
> I think the code below will get this issue fixed.
>
> Mark Ivey
>
> Sub test2()
> * * 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 Long * ' changed type due to value being a long
> Number
>
> * * 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)
> * * * * * * * * 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(J)
> * * * * * * * * 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

Thanks a lot Mark for your help you really solved my problem. Just a
small question that if i have percentages in decimals like for example
A_Percents(1) = 7.69
A_Percents(2) = 8.59
A_Percents(3) = 10
A_Percents(4) = 8
A_Percents(5) = 9.44
A_Percents(6) = 6
A_Percents(7) = 7.15
A_Percents(8) = 20.02
A_Percents(9) = 3.33
A_Percents(10) = 5.58
A_Percents(11) = 10.23
A_Percents(12) = 3.97
and when these percentages get multiplied by the amount we say like
2170 then obvesly i'll get result in decimals. Is there way that we
can round figures just doing some change in code "Sheets(2).Cells(k,
5).Value = (A_Percents(J) / 100) *
Sheets(1).Cells(i, 5).Value" in above macro so i can get figures
rounded to near 10 or 100. i know i am asking to many question but
please this is the last bit. i try to ask question in bits on this
google group in case if someone dont understand my big question. once
again thanks a lot for you all help
 
Reply With Quote
 
Mark Ivey
Guest
Posts: n/a
 
      19th Apr 2008
Not totally sure about the rounding bit just yet, but the code below will
get rid of the decimal point for now...


Mark Ivey



Sub test3()

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 Long ' changed type due to value being a long
Number


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) = 7.69
A_Percents(2) = 8.59
A_Percents(3) = 10
A_Percents(4) = 8
A_Percents(5) = 9.44
A_Percents(6) = 6
A_Percents(7) = 7.15
A_Percents(8) = 20.02
A_Percents(9) = 3.33
A_Percents(10) = 5.58
A_Percents(11) = 10.23
A_Percents(12) = 3.97



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, 5).NumberFormat = "0"
Sheets(2).Cells(k, 6).Value = Sheets(1).Cells(i, 6).Value
Sheets(2).Cells(k, 7).Value = MTH(J)
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 With Quote
 
Mark Ivey
Guest
Posts: n/a
 
      19th Apr 2008
K,

Just figured out a workaround....

If we use the worksheet function to round the result, it will round it to
the closest 10's place. Take a look at the following code.

Mark Ivey


Sub test4()

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 Long ' changed type due to value being a long
Number


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) = 7.69
A_Percents(2) = 8.59
A_Percents(3) = 10
A_Percents(4) = 8
A_Percents(5) = 9.44
A_Percents(6) = 6
A_Percents(7) = 7.15
A_Percents(8) = 20.02
A_Percents(9) = 3.33
A_Percents(10) = 5.58
A_Percents(11) = 10.23
A_Percents(12) = 3.97



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 = Application.WorksheetFunction
_
.Round((A_Percents(J) / 100) * Sheets(1).Cells(i,
5).Value, -1)

Sheets(2).Cells(k, 6).Value = Sheets(1).Cells(i, 6).Value
Sheets(2).Cells(k, 7).Value = MTH(J)
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 With Quote
 
 
 
Reply
Page 1 of 2 1 2

« totally newbie question about macros and programing it | Insert a line at the bottom page »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro recorded... tabs & file names changed, macro hangs Steve Microsoft Excel Worksheet Functions 3 30th Oct 2009 11:41 AM
::: Sort macro with empty lines at the end. ::: Macro de tri avec lignes vide en bas. infojacques@gmail.com Microsoft Excel Discussion 2 5th Jul 2007 11:40 AM
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor karll@swfab.com Microsoft Excel Programming 2 30th Mar 2007 07:48 PM
Run Macro On Open event for report and SetValue Macro has wierd error ThompsonJessical@yahoo.com Microsoft Access Macros 2 2nd Aug 2005 05:51 PM
Start Macro / Stop Macro / Restart Macro Pete Microsoft Excel Programming 2 21st Nov 2003 05:04 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 05:20 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top