Split words in each cell in range and compare to see if in another range

[~!@#$%]

Hi Guys ,
What i'm trying to do is loop through my range eg: K15:K150 which is made up of comma seperated words and check each word in the cell against a name in another range B15:B150, if it's there ill save its address in another Range (Z) so i have a cell of adresses for that cell it was checking.
so
VB:
Col B ColK ColZ

Jon Rick,Ben B4,B5
Dave
Rick Jon,Dave B2,B3
Ben
Todd Jon B2

I only started splitting strings for single cell, so wouldn't really be of help to you guys here
but was trying to adapt:
VB:
Sub SplitValue()
Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
LookInHere = Range("K15").Value
SplitCatcher = Split(LookInHere, ",")
For Counter = 0 To UBound(SplitCatcher)
If SplitCatcher(Counter) = Range("B15:B150").Text Then
'get address and store in Z
End If
Next
End Sub

any ideas guys

[patel]

Sub SplitValue()
Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
For r = 15 To 19
LookInHere = Range("K" & r).Value
SplitCatcher = Split(LookInHere, ",")
For Counter = 0 To UBound(SplitCatcher)
For Each cell In Range("B15:B19")
If SplitCatcher(Counter) = cell Then
s = s & cell.Address(False, False) & ","
Exit For
End If
Next
Range("Z" & r) = Left(s, Len(s) - 1)
Next
s = ""
Next
End Sub

[Doug Robbins]

Use Dim i As Long, j As Long, n As Long
Dim arrnames As Variant
Dim strcells As String
With ActiveSheet.Range("A1")
For i = 14 To 149
arrnames = Split(.Offset(i, 10), ", ")
strcells = ""
For j = LBound(arrnames) To UBound(arrnames)
For n = 14 To 149
If arrnames(j) = .Offset(n, 1) Then
strcells = strcells & ", B" & n + 1
End If
Next n
Next j
If strcells <> "" Then
strcells = Mid(strcells, 3)
.Offset(i, 25) = strcells
End If
Next i
End With

[~!@#$%]

:beerchug:
Thanks for your time guys, much appreciated!

[Tom Jones]

Cross posted:

Split words in each cell in range and compare to see if in another range (http://www.ozgrid.com/forum/showthread.php?t=178925)

[snb]

or


Sub M_snb()
for each it in columns(2).specialcells(2)
columns(11).replace it,it.address(0,0)
next
end sub

[patel]

snb code is very good, but to save in column z as requested

Sub M_snb()
Columns(11).Copy Columns(26)
For Each it In Columns(2).SpecialCells(2)
Columns(26).Replace it, it.Address(0, 0)
Next
End Sub

[Tom Jones]

Hi,


or


Sub M_snb()
for each it in columns(2).specialcells(2)
columns(11).replace it,it.address(0,0)
next
end sub



If in column B is Cristal and Cris or Ben and Beneton code does not work correct. Can you correct the code?

[snb]

You must have overlooked post #1

[Tom Jones]

You must have overlooked post #1

It seems you do not understand.
In this range B15:B150 can be name as I mention before:
Cristal,
Cris
Ben
Beneton

ect..

and in column K will be wrong answer. Try and see...

[snb]

No such name in post #1

[SamT]

Hi,

If in column B is Cristal and Cris or Ben and Beneton code does not work correct. Can you correct the code?

Your question is wrong. Can you correct the question?

[Tom Jones]

In this range B15:B150 can be name as I mention before:
Cristal
Jon
Cris
Ben
Dave
Beneton
Rick



If the name is that the above result snb's code is wrong.

[Aussiebear]

Where is the code wrong? Can you name the line it defaults on?

[SamT]

In this range B15:B150 can be name as I mention before:
Cristal
Jon
Cris
Ben
Dave
Beneton
Rick



If the name is that the above result snb's code is wrong.
Even with those names in column B, snb's code does exactly what the OP asked for.

If you want code that does something different, you need to say what you want the code to do.

[Tom Jones]

Hi,

@SamT
In the attached file I marked in red incorrect result SNB's code. I don't know if you tested and say is good with given example.

@ Aussiebear
No code is incorrect (and obviously not an error code) but the result code.

Hope you will understand now.

[SamT]

You are a good man, Tom Jones; Stubborn. I like that.:beerchug:

BTW, did I mention that you are correct and I was wrong.:oops:

[Tom Jones]

You are a good man, Tom Jones; Stubborn. I like that.:beerchug:

BTW, did I mention that you are correct and I was wrong.:oops:

Hi,

Perhaps my expression was not explicit enough but...
I am glad that in the end you understood.