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
 

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
506
thank you all that helped me i have decided to use fluffs code
just need to do some tweeking
thanks again most helpful
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,865
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
506
hi guys i have failed miserably to get this to work i have attatched a snap shot of my worksheet with intended result being the 3rd box
what i need code to do is lookup first name in cell a2 and check to see if it is in b2 if it is then check if interior color in c2 matches c1 it does not
so then checks d2 and d1 now this matches so j2=j2+1 now we go back and check if a2 is in b3 then a2 is in b4 etc then the code will start all over again
now checking 2nd name in cell a3 with cell b2 and b3 and b4 and so on
i think the main problem is in checking the name when using key f8 to step through the code and hover over fnd it displays the four names
Annotation 2020-04-30 170547.png

Dim Fnd As Range
Dim a, x, name As Variant
a = 2: name = 2: x = 0
again:
x = x + 1

Set Fnd = Range("b:b").Find(Range("a" & name).Value, , , xlPart, , , False, , False)

If Fnd Is Nothing Then a = a + 1: GoTo again:






If Range("c" & a).Interior.ColorIndex = Range("c1").Interior.ColorIndex Then Range("i" & a) = Range("i" & a) + 1
If Range("d" & a).Interior.ColorIndex = Range("d1").Interior.ColorIndex Then Range("j" & a) = Range("j" & a) + 1
If Range("e" & a).Interior.ColorIndex = Range("e1").Interior.ColorIndex Then Range("k" & a) = Range("k" & a) + 1
If Range("f" & a).Interior.ColorIndex = Range("f1").Interior.ColorIndex Then Range("l" & a) = Range("l" & a) + 1
If Range("g" & a).Interior.ColorIndex = Range("g1").Interior.ColorIndex Then Range("m" & a) = Range("m" & a) + 1


a = a + 1
If x = 60 Then Exit Sub
If a = 12 Then a = 2: name = name + 1
GoTo again:
this is as far as i have got with the code a bit messy i know but just toying with it at the moment
any help will be greatly appreciated tia
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,865
Office Version
  1. 365
Platform
  1. Windows
As this is now a totally different question, you need to start a new thread. Thanks
 

Forum statistics

Threads
1,136,206
Messages
5,674,410
Members
419,507
Latest member
karenvanegas

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