Results 1 to 22 of 22

Group sort candidates

Threaded View

  1. #8
    Forum Contributor
    Join Date
    04-19-2010
    Location
    Manchester
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Group sort candidates

    Sorry Dave where would I add/amend this code?

    Here is the 1st and 2nd macro at the moment
       Sub FirstMacro()
        Dim sh As Worksheet, ws As Worksheet, sh2 As Worksheet
        Dim gp1 As Range, gp2 As Range, gp3 As Range, gp4 As Range
        Dim rng As Range, c As Range
        Dim rw, cl, x
        Dim CntRng As Range
        Set sh = Sheets("groups")
        Set ws = Sheets("data")
        Set sh1 = Sheets("candidates")
        
        With sh1
            Set CntRng = .Range("C:C,H:H")
        End With
        
        With sh
            Set gp1 = .Cells.Find("Group 1")
            Set gp2 = .Cells.Find("Group 2")
            Set gp3 = .Cells.Find("Group 3")
            Set gp4 = .Cells.Find("Group 4")
            .Range("D7:F61,J7:L61,J28:L82,D28:F82").ClearContents
            .Pictures.Delete
        End With
    
        x = 2
        With ws
            Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            For Each c In rng.Cells
                Set ct = CntRng.Find(c.Offset(, -1))
                If Not ct Is Nothing Then
                    If c = "Group 1" Then
                        rw = gp1.Row + 1
                        cl = gp1.Column + 3
                        sh.Cells(rw + x, cl) = c.Offset(, -1) 'admin number
                        sh.Cells(rw + x, cl - 1) = c.Offset(, -2) 'name
                        sh.Cells(rw + x, cl + 1) = c.Offset(, 2) 'score
                        x = x + 2
                    End If
                End If
            Next c
    
            x = 2
            For Each c In rng.Cells
                Set ct = CntRng.Find(c.Offset(, -1))
                If Not ct Is Nothing Then
                    If c = "Group 2" Then
                        rw = gp2.Row + 1
                        cl = gp2.Column + 3
                        sh.Cells(rw + x, cl) = c.Offset(, -1) 'admin number
                        sh.Cells(rw + x, cl - 1) = c.Offset(, -2) 'name
                        sh.Cells(rw + x, cl + 1) = c.Offset(, 2) 'score
                        x = x + 2
                    End If
                End If
            Next c
    
            x = 2
            For Each c In rng.Cells
                Set ct = CntRng.Find(c.Offset(, -1))
                If Not ct Is Nothing Then
                    If c = "Group 3" Then
                        rw = gp3.Row + 1
                        cl = gp3.Column + 3
                        sh.Cells(rw + x, cl) = c.Offset(, -1) 'admin number
                        sh.Cells(rw + x, cl - 1) = c.Offset(, -2) 'name
                        sh.Cells(rw + x, cl + 1) = c.Offset(, 2) 'score
                        x = x + 2
                    End If
                End If
            Next c
            x = 2
            For Each c In rng.Cells
                Set ct = CntRng.Find(c.Offset(, -1))
                If Not ct Is Nothing Then
                    If c = "Group 4" Then
                        rw = gp4.Row + 1
                        cl = gp4.Column + 3
                        sh.Cells(rw + x, cl) = c.Offset(, -1) 'admin number
                        sh.Cells(rw + x, cl - 1) = c.Offset(, -2) 'name
                        sh.Cells(rw + x, cl + 1) = c.Offset(, 2) 'score
                        x = x + 2
                    End If
                End If
            Next c
     
        End With
    
        SecondMacro
    
    
    End Sub
    
    Sub SecondMacro()
        Dim sh As Worksheet, ws As Worksheet
        Dim rng As Range, c As Range
        
        Dim fndRng As Range
        
        Set sh = Sheets(2)
        Set ws = Sheets(1)
        
        With sh
            Set rng = .Range("E:E,K:K").SpecialCells(xlCellTypeConstants, 1)
            For Each c In rng.Cells
                With ws
                    Set fndRng = .Cells.Find(c, LookIn:=xlValues)
                    If Not fndRng Is Nothing Then
                        fndRng.Offset(, 1).Copy c.Offset(, -2)
                    End If
                End With
            Next c
        End With
    End Sub
    Last edited by technik; 06-05-2020 at 09:49 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sort then insert entire row then sort again by group
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2018, 09:16 PM
  2. sort by group and total by each group
    By mheinemann in forum Excel General
    Replies: 3
    Last Post: 04-30-2015, 11:48 AM
  3. Replies: 1
    Last Post: 04-18-2014, 05:54 PM
  4. Replies: 1
    Last Post: 10-19-2012, 07:55 AM
  5. Sort a group of names based on the group total
    By ron2k_1 in forum Excel General
    Replies: 3
    Last Post: 08-13-2010, 01:16 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