Excel公式/VBA在其他工作表中搜索部分字符串 [英] Excel Formula/VBA to search partial strings in other sheet

问题描述

我在工作表1的两个单元格中有名字(例如:B1(吉娜·威廉姆斯)和B2(帕特里克·拉夫特))，相应的银行对帐单叙述在工作表2(C列)中，例如:从吉娜存款到租金的位置".

I am having names in two cells of sheet1 (e.g. : B1 (Gina Williams) & B2 (Patrick Rafter)) and the corresponding bank statement narratives are in sheet 2 (column C) e.g: "Deposit from Gina towards rent for connaught place apt".

现在，我需要搜索单元格B1&中的所有四个部分文本.工作表1的B2(即工作表2的整个B列中的吉娜"，威廉斯"，帕特里克"，"after夫").如果存在匹配项，我需要捕获相应的B列和D值匹配的行.

Now I need to search all the four partial texts available in cells B1 & B2 of sheet 1 (ie. "Gina", "Williams", "Patrick", "Rafter" in the entire column B of sheet 2. if there is a match i need to capture the corresponding column B & D value for the matching row.

SHEET1

Column A      Column B            Column C                         Column D
   1        GINA WILLIAMS     OUTPUT (matching col b of sheet2)  OUTPUT (matching col D of sheet2)
   2        PATRICK RAFTER    OUTPUT (matching col b of sheet2)  OUTPUT (matching col D of sheet2) 

SHEET2

Column A   Column B     Column C                                              Column D
    1     12/7/2015   Deposit from Gina towards rent for connaught place apt        320 

    2     13/7/2015   Deposit from Rafter towards rent for connaught place apt          720

我尝试使用vlookup，find，match(以及left，right，mid函数)功能.

I have tried with vlookup, find, match (along with left, right, mid functions) functions.

推荐答案

我为您准备了一个.我已经测试了代码.它非常适合我.

I got one for you. I already tested the code. It work perfectly for me.

但是，不是重复命名的受让人，这意味着，对于重复的名称和重复的存款，它不能给出正确的结果.

But, not grantee for duplicate naming , means, it can't give right result for duplicate names and duplicate deposit.

代码在这里:

Sub findAndGet()

    Dim sh1, sh2 As Worksheet
    Dim tempRow1, tempRow2 As Integer
    Dim strList() As String
    Dim name As String
    Dim index As Integer

    'Set sheets
    Set sh1 = Sheets("list")
    Set sh2 = Sheets("search")

    'Set the start row of Sheet1
    tempRow1 = 1

    'Loop all row from starRow until blank of column A in Sheet1
    Do While sh1.Range("A" & tempRow1) <> ""

        'Get name
        name = sh1.Range("B" & tempRow1)

        'Split by space
        strList = Split(Trim(name), " ")

        'Set the start row of Sheet2
        tempRow2 = 1

        'Reset flag
        isFound = False

        'Loop all row from startRow until blank of column A in Sheet2
        Do While sh2.Range("A" & tempRow2) <> ""

            For index = LBound(strList) To UBound(strList)

                'If part of name is found.
                If InStr(UCase(sh2.Range("C" & tempRow2)), UCase(strList(index))) > 0 Then

                    'Set true to search flag
                    isFound = True

                    'exit do loop
                    Exit Do

                End If

            Next index

            'Increase row
            tempRow2 = tempRow2 + 1

        Loop

        'If record is found, set output
        If isFound Then

            'set date
            sh1.Range("C" & tempRow1) = sh2.Range("B" & tempRow2)

            'set amount
            sh1.Range("D" & tempRow1) = sh2.Range("D" & tempRow2)

        End If

        'Increase row
        tempRow1 = tempRow1 + 1

    Loop

End Sub

这篇关于Excel公式/VBA在其他工作表中搜索部分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！