![]() |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
|
|
#1
|
|||
|
|||
|
I am looking for help with the following;
I have two sheets; 1. Skill Map 2. Person Map I would like to create a macro to look up each name in column A on 'Person Map', in sheet 'Skill Map'. If it finds a result, I would like it to return the row AND column name, within the same cell, next to the name on 'Person Map'. The complication is that there could be multiple names in one cell on 'Skill Map'. I have tried to pull something together but my knowledge isnt substantial enough. Help would be much appreciated.
|
|
#2
|
|||
|
|||
|
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
|
|
#3
|
|||
|
|||
|
Wow! Thank you!! It works perfectly. This will save me so much time!
|
|
| Tags |
| find, vba macro |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Search for multiple texts in cell, return specific text
|
mariur89 | Excel | 4 | 12-14-2014 01:33 AM |
| Return Sum value of one column from cells not blank in another column | zulugandalf | Excel | 3 | 08-14-2014 03:37 AM |
Help Coloring a cell in Sheet one if data is missing from another sheet
|
Aeducan | Excel | 1 | 06-22-2014 04:49 PM |
| as the return value of another column, using Listbox? | marreco | Excel Programming | 1 | 03-27-2012 12:48 PM |
Need to search a column for a macth and return a result from a third column
|
pdfaust | Excel | 2 | 02-03-2011 03:02 PM |