1. The 1004 error with the
WorksheetFunction.Search
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
Else
End If
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
Next