Macro to find if all values in an array is present

Question

I have a sheet named Assignee which contains the names of certain persons. I have another sheet named Raw which contains multiple rows containing text strings. My requirement is to find out if any of the names in sheet Assignee is present in a cell, and if so, which is the last name in that cell. To specify more, see following example,

Assignee sheet contains names Vivek S. Panicker in cell A1, John Smith in A2 and William Dezuza Margeret in A3. Raw sheet A1 cell contains a text string like, "John Smith met me last night to inquire about William Dezuza Margeret". The last name in this string is "William Dezuza Margeret", which I need to extract using a VBA code. Since this need to be done in multiple lines, a macro with loop is highly appreciated.

Answer 1

Function LastUsedName(rng As Range) As Variant

    Dim names As Variant
    names = Sheets("Assignee").Range("A1:A" & Sheets(2).Range("A" & Rows.Count).End(xlUp).Row)

    Dim v As Variant
    For Each v In names
        If InStr(1, rng, v, vbTextCompare) Then
            Dim pos As Long
            pos = InStrRev(rng, v, -1, vbTextCompare)
            LastUsedName = Mid(rng, pos, Len(v))
            Exit Function
        End If
    Next

End Function