Excel Vba Compare 2 strings but only return full words found

[diamanthian1]

I have 2 worksheets with lists of strings in each
worksheet"validation"
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Wolf Team[/TD]
[/TR]
[TR]
[TD]Bite Back[/TD]
[/TR]
[TR]
[TD]Walk Alone[/TD]
[/TR]
</tbody>[/TABLE]

Worksheets"data"
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AARDWOLF[/TD]
[/TR]
[TR]
[TD]ABALONE[/TD]
[/TR]
[TR]
[TD]ABACK[/TD]
[/TR]
</tbody>[/TABLE]




I running some code to test if any word in "Validation" occurs in "Data" and for each occurrence the individual word found should be placed in the next column in the "Data" sheet and the whole String should be placed in column after that.
I have written the following code which I thought would do it

[FONT=arial]Sub searchStringTest()[/FONT]
[FONT=arial]  Dim i As Long, j As Long[/FONT]
[FONT=arial]  Dim ws1 As Worksheet, ws2 As Worksheet[/FONT]
[FONT=arial]  Dim WrdArray() As String[/FONT]
[FONT=arial]  Dim text_string As String[/FONT]

[FONT=arial]  Set ws1 = Sheets("Validation")[/FONT]
[FONT=arial]  Set ws2 = Sheets("Data")[/FONT]

[FONT=arial]  For i = 1 To ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row[/FONT]
[FONT=arial]    For j = 1 To ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row[/FONT]
[FONT=arial]        text_string = UCase(ws1.Range("A" & j))[/FONT]
[FONT=arial]        WrdArray() = Split(text_string)[/FONT]
[FONT=arial]            For K = LBound(WrdArray) To UBound(WrdArray)[/FONT]
[FONT=arial]        If InStr(1, UCase(ws2.Range("A" & i)), WrdArray(K)) Then[/FONT]
[FONT=arial]         ws2.Range("B" & i) = WrdArray(K)[/FONT]
[FONT=arial]         ws2.Range("C" & i) = text_string[/FONT]
[FONT=arial]        Else[/FONT]


[FONT=arial]        End If[/FONT]
[FONT=arial]        Next K[/FONT]


[FONT=arial]    Next j[/FONT]
[FONT=arial]  Next i[/FONT]

[FONT=arial]  Set ws1 = Nothing: Set ws2 = Nothing[/FONT]
[FONT=arial]End Sub[/FONT]

but instead of any occurrences of the full words I am getting
Worksheets"data"
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AARDWOLF[/TD]
[TD]Wolf[/TD]
[TD]Wolf Team[/TD]
[/TR]
[TR]
[TD]ABALONE[/TD]
[TD]Alone[/TD]
[TD]Walk Alone[/TD]
[/TR]
[TR]
[TD]ABACK[/TD]
[TD]Back[/TD]
[TD]Bite Back[/TD]
[/TR]
</tbody>[/TABLE]

So I am getting Stings that are contained within the words, instead of just the words.
Any Ideas I am still new to coding and am pretty sure I have just missed something simple

 

[JoeMo]

What is the result for the sheet "data" you want for the sample data you have posted?

 

[diamanthian1]

For the examples given it should be
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]AARDWOLF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABALONE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABACK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

as none of them should match

 

[JoeMo]

For the examples given it should be
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]AARDWOLF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABALONE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABACK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

as none of them should match

That seems to be the reverse of your original request: -->test if any word in "Validation" occurs in "Data".
Do you mean: test if any word in "Data" occurs in "Validation"?

 

[diamanthian1]

Thanks for replying Joe,
The code is correct, it's just not doing what I need
none of them should match as AARDWOLF does not equal WOLF, ABALONE does not equal ALONE
WOLF is within AARDWOLF, but that is not what I am trying to achieve
if you see what I mean

 

[JoeMo]

Thanks for replying Joe,
The code is correct, it's just not doing what I need
none of them should match as AARDWOLF does not equal WOLF, ABALONE does not equal ALONE
WOLF is within AARDWOLF, but that is not what I am trying to achieve
if you see what I mean

I think you need to reverse the order of comparison in your code. Your code should ask if any word in sheet "Data" is found in the sheet "Validation" and it seems to be doing the reverse.