+ Reply to Thread
Results 1 to 4 of 4

Looping Through Worksheet Finding Matching Data

Hybrid View

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Looping Through Worksheet Finding Matching Data

    Hello, I stuck on this problem, I have included a sample worksheet of what my data looks like (tab1)and what I would like the data to look like(tab2) tab 3 is just a reset in case the script messes up the data.

    What I am trying to do is look at col A and B and if those same values are found later on down the list and the date is not the same as the one found, move the found time into the same col and row and delete the found values from col a , col b and the start time and stop time.

    It is difficult to explain is why I included a spreadsheet to help. Here is the script I'm working with. It appears to work till a duplicate isn't found then it starts to mess up.

    Thanks for any help, Mike

    With sheets(1)
    LROW = .Cells(Rows.Count, 1).End(xlUp).Row
                For r = LROW To 6 Step -1
                    For c = 3 To 15 Step 2
                                If .Cells(r, c) <> vbNullString Then
                                    For r2 = 6 To LROW
                                        If .Cells(r2, 1) = .Cells(r, 1) And .Cells(r2, 2) = .Cells(r, 2) And r <> r2 And .Cells(r2, c) = vbNullString Then
                                            .Cells(r2, c) = .Cells(r, c)
                                            .Cells(r2, c).NumberFormat = "[$-409]h:mm AM/PM;@"
                                            .Cells(r2, c + 1) = .Cells(r, c + 1)
                                            .Cells(r2, c + 1).NumberFormat = "[$-409]h:mm AM/PM;@"
                                            .Cells(r, c) = ""
                                            .Cells(r, c + 1) = ""
                                            .Cells(r, 1) = ""
                                            .Cells(r, 2) = ""
                                            GoTo 10
                                        End If
                                    Next r2
                                End If
                    Next c
    10
                Next r
    end with
    Attached Files Attached Files
    Last edited by realniceguy5000; 08-07-2015 at 04:36 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Looping Through Worksheet Finding Matching Data

    Try this...

    Sub Rest_Data()
        'Reset data for testing
        Sheets(1).Range("A7:P16").Value = Sheets(3).Range("A7:P16").Value
    End Sub
    
    
    Sub RNG()
        
        Dim v As Variant, r As Long, r2 As Long, c As Long
            
        With Sheets(1)
        
            v = .Range("A7:P" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
            
            For r = 1 To UBound(v, 1) - 1
                If v(r, 1) <> "" Then
                    For r2 = r + 1 To UBound(v, 1)
                        If v(r2, 1) = v(r, 1) And v(r2, 2) = v(r, 2) Then
                        
                            For c = 3 To UBound(v, 2) Step 2
                                    
                                If v(r2, c) <> "" And v(r, c) = "" Then
                                    
                                    v(r, c) = v(r2, c)
                                    v(r, c + 1) = v(r2, c + 1)
                                    
                                    v(r2, 1) = ""
                                    v(r2, 2) = ""
                                    v(r2, c) = ""
                                    v(r2, c + 1) = ""
                                    
                                    Exit For
                                    
                                End If
                            Next c
                        End If
                    Next r2
                End If
            Next r
            
            '.Unprotect "tomcat9765"
            Application.ScreenUpdating = False
            .Range("A7:P" & .Cells(Rows.Count, 1).End(xlUp).Row).Value = v
            .Range("C7:P" & .Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "[$-409]h:mm AM/PM;@"
            With .Range("A7", .Cells(Rows.Count, 1).End(xlUp))
                If Application.CountBlank(.Cells) Then .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            End With
            Application.ScreenUpdating = True
            '.Protect "tomcat9765"
            
        End With
    End Sub
    Last edited by AlphaFrog; 08-07-2015 at 04:24 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Looping Through Worksheet Finding Matching Data

    This should do the trick, Thanks so much for taking the time to help out...

    Great work,
    Mike

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Looping Through Worksheet Finding Matching Data

    You're welcome. Thanks for the feedback.

+ 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. Finding Matching Data in one Column/Adding corresponding matching string value.
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2013, 07:23 PM
  2. Looping through data and finding cells that meet criteria
    By pbarry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2013, 10:11 PM
  3. Replies: 3
    Last Post: 09-12-2013, 06:50 PM
  4. matching and finding data between two spreadsheets
    By Megatron in forum Excel General
    Replies: 5
    Last Post: 07-23-2012, 02:49 PM
  5. Replies: 2
    Last Post: 08-06-2009, 06:04 PM
  6. Looping and Matching Data Problem
    By l8sk8r in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2009, 07:33 AM
  7. finding or matching data
    By langdon37 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-09-2008, 02:25 PM
  8. Finding matching data
    By solnajeff in forum Excel General
    Replies: 5
    Last Post: 11-30-2007, 11:58 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