|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find text within cell and return column and row title next to the name on a new sheet.
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 |