1. The 1004 error with the
method simply means it was unable to find the text. You could wrap the call in some error handling e.g.
Dim loc As Integer
On Error Resume Next
loc = Application.WorksheetFunction.Search(Sheet2.Range("A2"), Range("A2"), 1)
If Err.Number > 1004 Then
2. You appear to be attempting to find things in a worksheet - that would mean you are using the wrong function - why do I believe this? You are attempting to assign the results of the search to a range - compare WorksheetFunction.Search method (Excel) | Microsoft Docs
] which returns an integer, versus Range.Find method (Excel) | Microsoft Docs
], which returns a range.
3. As for the loop - there are many ways - see Solution 1 and please do a little research for yourself before asking questions like that. I would probably go for
Dim sSearchFor As String, rng As Range
For Each rng In ThisWorkbook.Sheets("Text").Range("A2:A4")
sSearchFor = rng.Value