Looping through delimited text and using VLOOKUP

[Cyberguy]

I am a bit stuck on this, as I am not a macro expert.

I have a worksheet with two columns. The first column holds the name of an
corporate email Distribution List (DL), the next column contains the members
belonging to the DL (with some additional detail). Each member cell can have
multiple names, delimited by New Line characters(LF, CHAR(10)).
e.g.
| A | B |
DL_Team1 Joe, Users, NZ
Fred, Users, AU
Jane, Users, NZ
DL_Team2 Bill, Users, NZ
Mary, Users, AU

On another worksheet I have a column of some selected team members to do a
VLOOKUP against, one member name per cell.
e.g.
| A |
Mary
Fred

How do I flag a DL if it contains any member from the team I am interested in?

Thanks. I really appreciate your help on this.

 

[Stefi]

Try this macro:

Sub test()
Dim teamcell As Range, teamrng As Range
rowsno = Range("A" & Rows.Count).End(xlUp).Row
Set teamrng = Range("A1:A" & rowsno)
Range("C" & rowsno).ClearContents
For Each teamcell In teamrng
namearray = Split(Range("B" & teamcell.Row), Chr(10))
nameno = UBound(namearray, 1) + 1
For n = 0 To nameno - 1
commapos = InStr(1, namearray(n), ",")
membername = Left(namearray(n), commapos - 1)
selmember = True
On Error GoTo notfound
namepos = Sheets("Sheet2").Columns("A:A").Find(What:=membername,
After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
If selmember Then Exit For
Next n
If selmember Then Range("C" & teamcell.Row) = "First selected
member: " & membername
Next teamcell
Exit Sub
notfound:
selmember = False
Resume Next
End Sub

Regards,
Stefi

„Cyberguy†ezt írta:

 

[Stefi]

You are right, my mistake!
You are welcome! Thanks for the feedback!
Stefi

„Cyberguy†ezt írta: