Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-17-2015, 07:05 AM
tanyabowring@live.co.uk tanyabowring@live.co.uk is offline Find text within cell and return column and row title next to the name on a new sheet. Windows 7 32bit Find text within cell and return column and row title next to the name on a new sheet. Office 2010 32bit
Novice
Find text within cell and return column and row title next to the name on a new sheet.
 
Join Date: Mar 2015
Posts: 2
tanyabowring@live.co.uk is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Macro example.xlsx (10.6 KB, 12 views)
Reply With Quote
  #2  
Old 03-17-2015, 12:11 PM
Snakehips Snakehips is offline Find text within cell and return column and row title next to the name on a new sheet. Windows 8 Find text within cell and return column and row title next to the name on a new sheet. 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
  #3  
Old 03-26-2015, 01:48 AM
tanyabowring@live.co.uk tanyabowring@live.co.uk is offline Find text within cell and return column and row title next to the name on a new sheet. Windows 7 32bit Find text within cell and return column and row title next to the name on a new sheet. Office 2010 32bit
Novice
Find text within cell and return column and row title next to the name on a new sheet.
 
Join Date: Mar 2015
Posts: 2
tanyabowring@live.co.uk is on a distinguished road
Default

Wow! Thank you!! It works perfectly. This will save me so much time!
Reply With Quote
Reply

Tags
find, vba macro



Similar Threads
Thread Thread Starter Forum Replies Last Post
Find text within cell and return column and row title next to the name on a new sheet. 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
Find text within cell and return column and row title next to the name on a new sheet. 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
Find text within cell and return column and row title next to the name on a new sheet. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft