VBA to search & fill out text in specific columns

[Sia]

Hello,
Looking for assistance for the following topic:

I am trying to create a code. That code should check if cell "C2", sheet ''test", contains one or more words from sheet "Search Values".
If the code finds a match , it should copy the matching word and paste it on the next cell in column "D" ( without repeatable ones ). The code go through the whole column "C", cell by cell : C3,C4,C5 etc. , and paste the matching words in respective column next to (column D3,D4,D5).

Please check the attached images for reference.
I would like to thanks a lot in advance to anyone who is able to advise me.

Regards,

 

[DanteAmor]

Try this

Sub search_fill()
  Dim i As Long, k As Long
  Dim a As Variant, b As Variant, c As Variant
  Dim s As String
  
  a = Sheets("test").Range("C2", Sheets("test").Range("C" & Rows.Count).End(3)).Value
  b = Sheets("Search Values").Range("A1", Sheets("Search Values").Range("A" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1), 1 To 1)
  
  For i = 1 To UBound(a, 1)
    s = ""
    For k = 1 To UBound(b, 1)
      If InStr(1, a(i, 1), b(k, 1), vbTextCompare) > 0 Then s = s & b(k, 1) & Chr(10)
    Next
    If s <> "" Then c(i, 1) = Left(s, Len(s) - 1)
  Next
  Sheets("test").Range("D2").Resize(UBound(c, 1)).Value = c
End Sub

 

[Sia]

Try this

Sub search_fill()
  Dim i As Long, k As Long
  Dim a As Variant, b As Variant, c As Variant
  Dim s As String
 
  a = Sheets("test").Range("C2", Sheets("test").Range("C" & Rows.Count).End(3)).Value
  b = Sheets("Search Values").Range("A1", Sheets("Search Values").Range("A" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1), 1 To 1)
 
  For i = 1 To UBound(a, 1)
    s = ""
    For k = 1 To UBound(b, 1)
      If InStr(1, a(i, 1), b(k, 1), vbTextCompare) > 0 Then s = s & b(k, 1) & Chr(10)
    Next
    If s <> "" Then c(i, 1) = Left(s, Len(s) - 1)
  Next
  Sheets("test").Range("D2").Resize(UBound(c, 1)).Value = c
End Sub

Hello Mr DanteAmor,

I truly appreciate your help. Lots of thanks, be blessed, that is exactly what i need.

Regards!