Compare a single name text string to a string with multiple names

[CBrowne]

I'm still fairly new to Excel VBA and I'm hoping this is a simple problem?
I have an excel worksheet with hundreds of people's last names.
I've provided the user with the ability to enter a last name and the system will search the worksheet and list all the entries with matching last name.
That works. However, when the last name in the worksheet has multiple names (from re-marriages, etc), it doesn't work.

An example of the worksheet:

COLUMN A (1) COLUMN B (2)
LAST NAME FIRST NAME

Brown John
Smith David
Churchill Jim
Sturr Churchill Barber Laura
King Daniel
Church Susan
Churchill Bill

The user enters the name they want in a userform into "TextBoxLastName"

My vba code loops thru the entire worksheet using the following code to find the matching last names:

If StrComp(Cells(RowNo, 1).Value, TextBoxLastName.Value) = 0 then
do stuff and list the person in a report

User enters Churchill in the TextBoxLastName.

The results I get are:
Churchill Jim
Churchill Bill

How do I change the String Compare to also pick up the person with last name = Sturr Churchill Barber?

Any help would be greatly appreciated!

Charlene

 

[Peter_SSs]

Try this line instead

If InStr(1, " " & Cells(RowNo, 1).Value & " ", " " & TextBoxLastName.Value & " ", vbTextCompare) > 0 Then

 

[CBrowne]

If InStr(1, " " & Cells(RowNo, 1).Value & " ", " " & TextBoxLastName.Value & " ", vbTextCompare) > 0 Then

Thank you Peter, it worked perfectly. Now I'll have to study-up on the solution and tuck it into my brain! Thanks for the learning experience too!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.