Hello All,
I am new to this forum and I am hoping some one can help me out in this matter.
What I like to achieve is:
- Loop through a worksheet row by row
- Based on an index value, copy the selected row multiple times and then split the cost by percentage assigned
- Write the result to a new worksheet
- Go back to the previous worksheet and go to the next row and process all over again based on the index
Basically this is to do a wage split for different divisions for a finance statement. The index is stored a the first column of the work sheet. Depending on the coding of index, I need to copy multiple times of the selected row depending on how many divisions the wage cost needs to split it into and then add another line to balance the total split amount to the original department.
I know this sounds a bit complex. So I copy the previous and after to help understand what my goal is:
Previous:
[TABLE="width: 1065"]
<tbody>[TR]
[TD]Div [/TD]
[TD]Account[/TD]
[TD]Amount [/TD]
[TD]Employee ID[/TD]
[TD]Employee Name [/TD]
[TD]Account Description [/TD]
[TD]Description [/TD]
[TD]Journal Description [/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60500[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]OTHER SALARIES[/TD]
[TD]Rise BW 08[/TD]
[TD]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60500[/TD]
[TD="align: right"]246.75[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]OTHER SALARIES[/TD]
[TD]Rise BW 09[/TD]
[TD]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60530[/TD]
[TD="align: right"]7.52[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]HOLIDAY PAY[/TD]
[TD]Rise BW 08[/TD]
[TD]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60530[/TD]
[TD="align: right"]9.87[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]HOLIDAY PAY[/TD]
[TD]Rise BW 09[/TD]
[TD]Rise BW 09-2776[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
After processing (Goal)
[TABLE="width: 744"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="244" style="width: 183pt; mso-width-source: userset; mso-width-alt: 8923;"> <col width="64" style="width: 48pt;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="64" style="width: 48pt;" span="2"> <col width="155" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5668;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Index[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Div [/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]Account[/TD]
[TD="class: xl65, width: 244, bgcolor: transparent"]Amount [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Employee ID[/TD]
[TD="class: xl65, width: 159, bgcolor: transparent"]Employee Name [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Account Description [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Description [/TD]
[TD="class: xl65, width: 155, bgcolor: transparent"]Journal Description [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]188[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]223[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.1[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]715[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.25[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]802[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.15[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]-94[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]246.75[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]223[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.1[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]715[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.25[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]802[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.15[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]-123.375[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
</tbody>[/TABLE]
Sorry for all these numbers but I just want to help understand the issue. Can this be done in Excel macro? If it can be done, can some one give me suggestions on how to start coding?
I know this involved a loop and if statement. Perhaps a basic structure to loop through each row embedded with an if statement is a good start. It is a bit too complex at this point to put my hands on. Please help! Thank you!
Wenny Z
I am new to this forum and I am hoping some one can help me out in this matter.
What I like to achieve is:
- Loop through a worksheet row by row
- Based on an index value, copy the selected row multiple times and then split the cost by percentage assigned
- Write the result to a new worksheet
- Go back to the previous worksheet and go to the next row and process all over again based on the index
Basically this is to do a wage split for different divisions for a finance statement. The index is stored a the first column of the work sheet. Depending on the coding of index, I need to copy multiple times of the selected row depending on how many divisions the wage cost needs to split it into and then add another line to balance the total split amount to the original department.
I know this sounds a bit complex. So I copy the previous and after to help understand what my goal is:
Previous:
[TABLE="width: 1065"]
<tbody>[TR]
[TD]Div [/TD]
[TD]Account[/TD]
[TD]Amount [/TD]
[TD]Employee ID[/TD]
[TD]Employee Name [/TD]
[TD]Account Description [/TD]
[TD]Description [/TD]
[TD]Journal Description [/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60500[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]OTHER SALARIES[/TD]
[TD]Rise BW 08[/TD]
[TD]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60500[/TD]
[TD="align: right"]246.75[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]OTHER SALARIES[/TD]
[TD]Rise BW 09[/TD]
[TD]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60530[/TD]
[TD="align: right"]7.52[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]HOLIDAY PAY[/TD]
[TD]Rise BW 08[/TD]
[TD]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD]737[/TD]
[TD]60530[/TD]
[TD="align: right"]9.87[/TD]
[TD="align: right"]2776[/TD]
[TD] Employee 1[/TD]
[TD]HOLIDAY PAY[/TD]
[TD]Rise BW 09[/TD]
[TD]Rise BW 09-2776[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
After processing (Goal)
[TABLE="width: 744"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="244" style="width: 183pt; mso-width-source: userset; mso-width-alt: 8923;"> <col width="64" style="width: 48pt;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="64" style="width: 48pt;" span="2"> <col width="155" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5668;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Index[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Div [/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]Account[/TD]
[TD="class: xl65, width: 244, bgcolor: transparent"]Amount [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Employee ID[/TD]
[TD="class: xl65, width: 159, bgcolor: transparent"]Employee Name [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Account Description [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Description [/TD]
[TD="class: xl65, width: 155, bgcolor: transparent"]Journal Description [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]188[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]223[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.1[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]715[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.25[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]802[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]188*0.15[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]-94[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 08[/TD]
[TD="bgcolor: transparent"]Rise BW 08-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]246.75[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]223[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.1[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]715[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.25[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent, align: right"]802[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent"]246.75*0.15[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: transparent"]737[/TD]
[TD="class: xl66, bgcolor: transparent"]60500[/TD]
[TD="bgcolor: transparent, align: right"]-123.375[/TD]
[TD="bgcolor: transparent, align: right"]2776[/TD]
[TD="bgcolor: transparent"] Employee 1[/TD]
[TD="bgcolor: transparent"]OTHER SALARIES[/TD]
[TD="bgcolor: transparent"]Rise BW 09[/TD]
[TD="bgcolor: transparent"]Rise BW 09-2776[/TD]
[/TR]
</tbody>[/TABLE]
Sorry for all these numbers but I just want to help understand the issue. Can this be done in Excel macro? If it can be done, can some one give me suggestions on how to start coding?
I know this involved a loop and if statement. Perhaps a basic structure to loop through each row embedded with an if statement is a good start. It is a bit too complex at this point to put my hands on. Please help! Thank you!
Wenny Z