[Solved] Macro/Function to find a string in a column and find it in a sentence in sheet 2 and return respective value in column 3

Question

First we will dim the variable

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

We need to set the worksheets before we set the ranges

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

Set the range for sheet1, this will determine the last row in columnD and set the range

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

This will set the range for sheet2

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

Now we will start a loop to find the check #’s

For Each a In Rng.Cells

Will want to find the 1st check number

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

If it is found then we want to place the date in column H

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

And put a “Y” in column H sheet1

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

If not found, then we want to place a “N” in column H sheet1

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

Then Loop to the next “a” in column D sheet1

    Next a

All together it will look like this.

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

Accepted Answer