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!
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