View Single Post
 
Old 03-17-2015, 12:11 PM
Snakehips Snakehips is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

TanyaBrowning,
Try the following.....

Code:
Sub Map_Peeps()
Dim Skills As Range
Dim GotIt As RangeDim LastC As Range
Dim First As String
Dim lr As Long, c As Long
Dim Who As String
Set Skills = Sheets("Skill Map").Range("A1:K11")  'edit to suit

With Sheets("Person Map")
    lr = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set People = .Range("A2:A" & lr)
End With

For Each Peep In People
    Who = Peep.Value
    c = 0
        With Skills
            Set LastC = .Cells(.Cells.Count)
        
            Set GotIt = .Find(what:=Who, after:=LastC)
        
            If Not GotIt Is Nothing Then First = GotIt.Address
       
            Do Until GotIt Is Nothing
                c = c + 1
                Peep.Offset(0, c) = .Cells(GotIt.Row, 1) & " " & .Cells(1, GotIt.Column)
                Set GotIt = .FindNext(after:=GotIt)
                If GotIt.Address = First Then Exit Do
            Loop
        End With
Next Peep

End Sub
Hope that helps.
Reply With Quote