VBA: Find Formula

I am using the following code to search in the A column of a row for a name. If the name is found, it is placed in a column 2 over. I am trying to search against a list of names rather than one name. Is it possible to add an array into what I have to search a list found in another sheet?

Code:

---

Sub SearchName()
    Application.ScreenUpdating = False
    With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
        .Formula = "=IF(MIN(FIND({""Text1"",9},A2&""C9""))<=LEN(A2),""Text1"")"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

---

To clarify, looking to make "text 1" a list of text in another sheet.
Cheers!

I am confused by what that formula is trying to do. Is A2&"C9" meant to be a range that is bounded by a cell string in C9?

Can you give an example of the data?

I was handed this code and now need to modify it. To be honest, I do not know what the C9 is all about, as there is nothing in that cell. On sheet 4 in my file is a column that has a paragraph in each cell. I want to search each paragraph for a name. The names I am looking for are stored in Sheet 2 column A. When a match is found, I want all the matches to be put in cell c1...

I have edited the code to be the following. It is close to what I want. The problem with this code is that Column A from sheet2 (column of names I want to be searched for) just ends up being copied to sheet4. There seems to be no searching going on. I want every row in column A of sheet 4 to be searched for the names in sheet2, when matched, all matched in that text will be placed in cell C of the same row. Please take a look and let me know your thoughts.

Cheers!

Code:

---

Sub test()
Dim ws1, ws2 As Worksheet, rng1, rng2, cel1, cel2 As Range
Dim i, lrow As Long
Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set ws2 = ThisWorkbook.Sheets("Sheet4")
'i only assumed that your data is both in column A of sheet 2 and 4
lrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = ws1.Range("A1:A" & lrow) 'this contains the Company names
lrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = ws2.Range("A1:A" & lrow) 'this contains list of text you want to search
i = 0
For Each cel2 In rng2
    For Each cel1 In rng1
        If InStr(cel1.Value, cel2.Value) <> 0 Then cel1.Copy ws2.Range("c1").Offset(i, 0): i = i + 1
    Next cel1
Next cel2
End Sub

---