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

740 IT屋

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.

解决方案
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

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



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


解决方案
 函数LastUsedName(rng As Range)As Variant 

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

Dim v As Variant
对于每个v名称
如果InStr(1,rng ,v,vbTextCompare)然后
Dim pos As Long
pos = InStrRev(rng,v,-1,vbTextCompare)
LastUsedName = Mid(rng,pos,Len(v))
退出函数
结束如果
下一个

结束函数

本文地址:IT屋 » 宏查找数组中的所有值是否存在于所选单元格中