Find a word within a sentence

[RoseChapman]

Hello,

I have a problem using VBA and I would really appreciate if someone could help.

I am using the following code to find the word "house" in any cell within column "L", if the word "house" is found within a sentence in a cell within column L copy whatever is in columns A, L and BX and paste them in another tab called "Paste".
My problem is I do not know how to add "find a sentence containing the word "house"" to the code. Could you please help me?. Many thanks. Rose

Sub Filtering()


Dim Lastrow As Long


With Sheets("Sheet1")

If .Range("L:L").Find("House", , xlValues, xlWhole, , , False) Is Nothing Then
MsgBox "No ""Changed"" rows found. ", , "No Rows Copied": Exit Sub
End If

Application.ScreenUpdating = False
Lastrow = .Range("BY" & Rows.Count).End(xlUp).Row
.Range("A1:BY" & Lastrow).AutoFilter Field:=76, Criteria1:="House"
Intersect(.AutoFilter.Range, .Range("A:A, L:L, BX:BX")).Copy

Sheets("Paste").Range("A:R").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
.AutoFilterMode = False


With Application
.CutCopyMode = False
.Goto Sheets("Outputs").Range("A:BX")
.ScreenUpdating = True
End With

End With
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback

 

[RoseChapman]

Always happy to contact Mr.Excel for prompt responses and always finding a solution to a problem. Highly recommend it!