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

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
506
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,887
Office Version
  1. 365
Platform
  1. Windows
As a start, try recording a macro to find the matching value and evaluate the resulting code?
 

Bagnon

New Member
Joined
Sep 28, 2005
Messages
37
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,135
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,860
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,135
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,887
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,136,205
Messages
5,674,396
Members
419,506
Latest member
mpazr001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top