need help with finding a name in a row where all the names are separated by commas

[kevin lazell]

hi guys n gals
i would like some help with finding a name in a cell where all the names are separated by commas
i would prefer a vba solution to this please.
there will be two or more names separated by comma like thus john smith,joe bloggs,jeff cooling,peter smith, etc
the search will be done using first and last name
so shall we say the name to be searched for is in a1 and the names are in b2
if result =true then go and do something
if result =false then look for name in b3 then b4 etc
the rest of it i can do it is just this first bit i cant get my head around
thanks in advance

 

[JackDanIce]

As a start, try recording a macro to find the matching value and evaluate the resulting code?

 

[Bagnon]

I've had good luck using stars * as wild cards with Vlookup and Match.

For VBA you can use the like function:

If ActiveCell.value like "*john*smith*" then ....

 

[Rick Rothstein]

Will you be doing the same "something" no matter what row that name is in? Or will the code need to know the row number in Column B where it is found?

 

[Fluff]

How about

Sub kevinlazell()
   Dim Fnd As Range
   
   Set Fnd = Range("B:B").Find(Range("A1").Value, , , xlPart, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox "Not found"
      Exit Sub
   End If
   'do something if found
End Sub

 

[Rick Rothstein]

How about...

The only problem I see with your code is the potential for a false positive. For example, if the name being searched for is "john smith" and that name does not exist in the list, but "john smithers" is in the list, your code will see that as a match.

 

[DataBlake]

This assumes you have headers and that the string in cell A2 is explicitly being searched through by cells B2, C2, D2, etc
whatever you want to happen when there is a match just replace 'do something or explain what you want to happen

Sub doathingy()
Dim i As Long, x As Long, lastcol As Long, lastrow As Long
Dim ary1 As Variant

ary1 = ary1 = ActiveSheet.UsedRange.Value2
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count

For i = 2 To lastrow
    For x = 2 To lastcol
   
        If InStr(ary1(i, 1), ary1(i, x)) > 0 Then
            'do something
            GoTo exitloop
        End If
   
    Next x
   
exitloop:
Next i


End Sub

 

[Fluff]

The only problem I see with your code is the potential for a false positive.

Agreed
But without some realistic data, it's hard to tell

 

[DataBlake]

The only problem I see with your code is the potential for a false positive. For example, if the name being searched for is "john smith" and that name does not exist in the list, but "john smithers" is in the list, your code will see that as a match.

I agree with Rick even my code will turn up false positives
edit: also is case sensitive

 

[JackDanIce]

I think if they record the code and post it back, some of the unknowns may be answered!
(.... As well as screenshot or better explanation of data etc)