Use Excel Macros to Loop through worksheet and process data

wzheng

New Member
Joined
May 18, 2018
Messages
2
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This seems to work.
Code:
Sub Breakout()
Dim WS As Worksheet
Dim NewWB As Workbook
Dim A As Long
Dim LastRow As Long
Dim LR As Long

Set WS = Worksheets(1)

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

    If LastRow > 1 Then
        Set NewWB = Workbooks.Add
        'Copy the headers
        WS.Range("A1:I1").Copy NewWB.Sheets(1).Range("A1")
        
        LR = 2
        For A = 2 To LastRow
            If .Range("A" & A) = "AA" Then
                WS.Range("A" & A & ":I" & A).Copy
                NewWB.Sheets(1).Range("a" & LR & ":I" & LR + 5).PasteSpecial xlPasteAll
                NewWB.Sheets(1).Range("B" & LR + 1) = "220"
                NewWB.Sheets(1).Range("D" & LR + 1) = NewWB.Sheets(1).Range("D" & LR) * 0.1
                NewWB.Sheets(1).Range("B" & LR + 2) = "713"
                NewWB.Sheets(1).Range("D" & LR + 2) = NewWB.Sheets(1).Range("D" & LR) * 0.25
                NewWB.Sheets(1).Range("B" & LR + 3) = "802"
                NewWB.Sheets(1).Range("D" & LR + 3) = NewWB.Sheets(1).Range("D" & LR) * 0.15
                NewWB.Sheets(1).Range("D" & LR + 4) = _
                    (NewWB.Sheets(1).Range("D" & LR) * 0.1 + _
                    NewWB.Sheets(1).Range("D" & LR) * 0.25 + _
                    NewWB.Sheets(1).Range("D" & LR) * 0.15) * -1
                LR = LR + 5
            End If
        Next
    End If
End With


End Sub
 
Upvote 0
Hi David,

Thank you very much for your posting. It seems it only returns the header of the column though.

Wenny Z

This seems to work.
Code:
Sub Breakout()
Dim WS As Worksheet
Dim NewWB As Workbook
Dim A As Long
Dim LastRow As Long
Dim LR As Long

Set WS = Worksheets(1)

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

    If LastRow > 1 Then
        Set NewWB = Workbooks.Add
        'Copy the headers
        WS.Range("A1:I1").Copy NewWB.Sheets(1).Range("A1")
        
        LR = 2
        For A = 2 To LastRow
            If .Range("A" & A) = "AA" Then
                WS.Range("A" & A & ":I" & A).Copy
                NewWB.Sheets(1).Range("a" & LR & ":I" & LR + 5).PasteSpecial xlPasteAll
                NewWB.Sheets(1).Range("B" & LR + 1) = "220"
                NewWB.Sheets(1).Range("D" & LR + 1) = NewWB.Sheets(1).Range("D" & LR) * 0.1
                NewWB.Sheets(1).Range("B" & LR + 2) = "713"
                NewWB.Sheets(1).Range("D" & LR + 2) = NewWB.Sheets(1).Range("D" & LR) * 0.25
                NewWB.Sheets(1).Range("B" & LR + 3) = "802"
                NewWB.Sheets(1).Range("D" & LR + 3) = NewWB.Sheets(1).Range("D" & LR) * 0.15
                NewWB.Sheets(1).Range("D" & LR + 4) = _
                    (NewWB.Sheets(1).Range("D" & LR) * 0.1 + _
                    NewWB.Sheets(1).Range("D" & LR) * 0.25 + _
                    NewWB.Sheets(1).Range("D" & LR) * 0.15) * -1
                LR = LR + 5
            End If
        Next
    End If
End With


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top