Use Excel Macros to Loop through worksheet and process data

W

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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

Tinbendr said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
Y
  • Question Question
Macro to copy values across rows and transposing them and add the user id
2
Replies
17
Views
2K
DanteAmor
DanteAmor
P
  • Question Question
Copying data from one col. to another
Replies
6
Views
566
Panoos64
P
K
  • Question Question
Macros to build Income statements
Replies
2
Views
953
Kazdima
K

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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