宏/函数，在一列中找到一个字符串，然后在工作表2中的一个句子中找到它，并在第3列中返回各自的值

首先，我们将变量变暗

Dim Rws As Long, Rng As Range, a As Range
Dim Rws2 As Long, rng2 As Range, c As Range
Dim sh As Worksheet, ws As Worksheet

我们需要先设置工作表，然后再设置范围

Set sh = Sheets("Sheet1")
Set ws = Sheets("Sheet2")

设置sheet1的范围，这将确定columnD的最后一行并设置范围

With sh
    Rws = .Cells(.Rows.Count, "D").End(xlUp).Row
    Set Rng = .Range(.Cells(2, "D"), .Cells(Rws, "D"))
End With

这将设置sheet2的范围

With ws
    Rws2 = .Cells(.Rows.Count, "D").End(xlUp).Row
    Set rng2 = .Range(.Cells(2, "D"), .Cells(Rws2, "D"))
End With

现在我们将开始循环以查找支票号

For Each a In Rng.Cells

想要找到第一个支票号码

  Set c = rng2.Find(what:=a, lookat:=xlPart)

如果找到，那么我们要将日期放置在H列中

        If Not c Is Nothing Then
        Cells(c.Row, "H") = a.Offset(0, -2)

并在H列1中放置一个“ Y”

 sh.Cells(a.Row, "H") = "Y"

如果未找到，那么我们要在H列中放置一个“ N” sheet1

 Else: sh.Cells(a.Row, "H") = "N"
end if

然后循环到D列中的下一个“ a” sheet1

    Next a

一起看起来会像这样。

Sub Loop2()
    Dim Rws As Long, Rng As Range, a As Range
    Dim Rws2 As Long, rng2 As Range, c As Range
    Dim sh As Worksheet, ws As Worksheet

    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")

    With sh
        Rws = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set Rng = .Range(.Cells(2, "D"), .Cells(Rws, "D"))
    End With

    With ws
        Rws2 = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set rng2 = .Range(.Cells(2, "D"), .Cells(Rws2, "D"))
    End With

    For Each a In Rng.Cells
        Set c = rng2.Find(what:=a, lookat:=xlPart)
        If Not c Is Nothing Then
            Cells(c.Row, "H") = a.Offset(0, -2)
            sh.Cells(a.Row, "H") = "Y"
        Else: sh.Cells(a.Row, "H") = "N"
        End If

    Next a

End Sub