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:

 

[Cyberguy]

Thanks Stefi.

Your code works perfectly, and it has also helped me understand a lot more
about how macro code can be structured.

 

[Cyberguy]

Here is my contribution back to the forum. I had trouble always replacing
semicolon delimiters with newline characters because the text was sometimes
too long for the formula. Here is my solution, loosely based on Stefi's
earlier code. Thanks Stefi for your really valuable help!!!

' Put newline character (LF) in place of every "; " delimiter in text
' Useful when text is too long for regular search and replace
Sub Newline_At_Semicolon()
Dim Workcell As Range, Workrng As Range
rowsno = Range("B" & Rows.Count).End(xlUp).Row
Set Workrng = Range("B1:B" & rowsno)
Range("D" & rowsno).ClearContents
For Each Workcell In Workrng
namearray = Split(Range("B" & Workcell.Row), "; ")
nameno = UBound(namearray, 1) + 1
membername = namearray(0)
For n = 1 To nameno - 1
membername = membername & Chr(10) & namearray(n)
Next n
Range("D" & Workcell.Row) = membername
Next Workcell
End Sub

 

[Cyberguy]

And just to show that I am paying attention in class, I spotted your
deliberate mistake ;-)

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

I think the 4th code line should read:
Range("C1:C" & rowsno).ClearContents

 

[Stefi]

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

„Cyberguy†ezt írta: