Lookup Name in Multiple Fields

[Rcaaa]

Hello,

I am working on a spreadsheet that has the following setup. What would be the best way to find out which projects each person is on? I was thinking of doing a vlookup, but I would like to know if there is a better alternative.

Project	Resource 1	Resource 2	Resource 3	Resource 4
1	Ana	Rob	Jim	Erica
2	Rob	Susan	Tim	Kyle

<tbody>
</tbody>

Thanks,

RCA

 

[alansidman]

With a worksheet that looks like this

v	A	B	C	D	E
1	Project	Resource 1	Resource 2	Resource 3	Resource 4
2	1	Ana	Rob	Jim	Erica
3	2	Rob	Susan	Tim	Kyle
4	3	Ana	Rob	Jim	Erica
5	4	Rob	Susan	Tim	Kyle
6	5	Ana	Rob	Jim	Erica
7	6	Rob	Susan	Tim	Kyle

The following code will tell you on Sheet 2 which projects a person is working on when you enter this code and follow the prompts

Option Explicit


Sub projectX()
    Dim lr As Long, lc As Long, c As Range
    Dim rng As Range, lr2 As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(2, 1), Cells(lr, lc))
    Dim crit As String, trow As Long
    crit = InputBox("Which person to search?")
    For Each c In rng
        If c = crit Then
            trow = c.Row
            lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
            Range("A" & trow).Copy Sheets("Sheet2").Range("A" & lr2 + 1)    'make sure you have set up a sheet2
        End If
    Next c
End Sub

 

[Rcaaa]

Thanks for sharing. Would you be able to recommend a resource to learn how to create VBA coding similar to this. I would like to learn how to do it for myself.

Thanks,

RCAAA

 

[alansidman]

Check out Mr Excel's books. I used one from 2010 but I am sure he has updated them. Go to the home page for this site.