宏查找数组中的所有值是否存在于所选单元格中 [英] Macro to find if all values in an array is present in a selected cell

问题描述

我有一张名为受让人的表，其中包含某些人的姓名。我有另一个名为Raw的工作表，其中包含多行包含文本字符串。我的要求是找出单元格中的任何名称是否存在于单元格中，如果是，则该单元格中的最后一个名称。要指定更多，请参见以下示例

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,

受让人表格包含单元格A1中的Vivek S. Panicker，A2中的John Smith和A3中的William Dezuza Margeret。原始图表A1单元格包含一个文本字符串，如约翰·史密斯昨晚遇见我，询问有关威廉德祖扎·玛格丽特。该字符串中的最后一个名字是William Dezuza Margeret，我需要使用VBA代码提取。因为这需要在多行中完成，所以宏非常感谢。

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.

推荐答案

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

这篇关于宏查找数组中的所有值是否存在于所选单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！