+ Reply to Thread
Results 1 to 2 of 2

VB Code to reorganize payroll data as explained

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-31-2012
    Location
    Georgia
    MS-Off Ver
    Excel 2010
    Posts
    194

    VB Code to reorganize payroll data as explained

    Hello:

    Please refer to attached file.
    I have data as shown in the sheet.
    Need VB Macro to go thru the data to give output as explained.

    Step 1 : Go thru Colunn A and look for text consisting of ","
    Once Found Copy the content of cell in Sheet2, starting at Cell A2
    This will be Employee Name Information.
    Now for this employee, Check data between text consisting "CHECK " and "This" and copy
    these data in sheet2 as shown. ( Row 8 and Row 9)

    Need to do this to end of the Sheet1.

    Let me know if you have any questions.
    Thanks.

    R
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: VB Code to reorganize payroll data as explained

    Hey. Here's the code I wrote. I hope it's close to what you want:

    Public Sub CopyEarnings()
    
    Dim oneSheet As Worksheet
    Dim twoSheet As Worksheet
    Dim lastRow As Long
    Dim thisRow As Long
    Dim nextRow As Long
    Dim checkRow As Long
    Dim totalRow As Long
    Dim loopRow As Long
    
    Application.ScreenUpdating = False
    Set oneSheet = Worksheets("Sheet1")
    Set twoSheet = Worksheets("Sheet2")
    lastRow = oneSheet.Cells(oneSheet.Rows.Count, "A").End(xlUp).Row
    nextRow = 2
    checkRow = 0
    totalRow = 0
    For thisRow = 1 To lastRow
        If Left(oneSheet.Cells(thisRow, "A").Value, 5) = "CHECK" Then checkRow = thisRow
        If Left(oneSheet.Cells(thisRow, "A").Value, 4) = "This" Then totalRow = thisRow
        If InStr(oneSheet.Cells(thisRow, "A").Value, ",") > 0 Then
            If checkRow > 0 And totalRow > 0 And (totalRow - checkRow) > 1 Then
                For loopRow = checkRow + 1 To totalRow - 1
                    If oneSheet.Cells(loopRow, "A").Value <> "" Then
                        twoSheet.Cells(nextRow, "A").Value = oneSheet.Cells(thisRow, "A").Value
                        twoSheet.Cells(nextRow, "B").Value = oneSheet.Cells(loopRow, "A").Value
                        twoSheet.Cells(nextRow, "C").Value = oneSheet.Cells(loopRow, "B").Value
                        nextRow = nextRow + 1
                    End If
                Next loopRow
            End If
            checkRow = 0
            totalRow = 0
        End If
    Next thisRow
    Application.ScreenUpdating = True
    
    End Sub
    WBD
    Office 365 on Windows 11, looking for rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VB Code to delete blank rows and reorganize Payroll Data
    By rmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2022, 06:52 AM
  2. [SOLVED] VB Code to reorganize data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2019, 10:21 PM
  3. VB Code to reorganize data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2019, 09:14 AM
  4. [SOLVED] VB Code to reorganize data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-01-2017, 04:31 PM
  5. [SOLVED] VB Code to reorganize data in a row
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2016, 04:41 PM
  6. [SOLVED] VB Code to reorganize data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2016, 08:57 PM
  7. VB Code to reorganize data..
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-19-2016, 11:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1