Help with code to determine whether two strings are in a cell

[Tiger91]

Hello, I'm pretty new with VBA and I'm not sure what I'm doing wrong.

I need to put all names in one page in a string array, then look through a row for a cell that has both kinds of names, and get the column # for that cell.

Example: Given the following input:





Tom
John
Bill
Jim


Jane








and this row



Tom, John
Tom & Bill
Tom & Jane
Jane and Jim



I need the code to be able to return the column where Jane (TestName) and Tom (InputArray) is located first (here col. C), then Jane and John, Jane and Tom, etc.

I'm using the following code:

i=1
Do While x = False
x = (InStr(ActiveSheet.Cells(i + 1, 1), TestName(0)) AND InStr(ActiveSheet.Cells(i + 1, 1), InputArray))
i = i + 1
Loop

It is supposed to stop when it finds the cell with both names, but it doesn't seem to be working. What do I need to do to fix it? Does it revolve around me using improper casting of strings?

Thanks

[SamT]

'This is not code!
Loop thru Columns
IF (InStr(ActiveSheet.Cells(Rw, Col), TestName) Then
For i = Lbound(InputArray to Ubound(InputArray)
If InStr(ActiveSheet.Cells(Rw, Col), InputArray(i)) then
Found = True
FoundColumn = Col
FoundRow = Rw
Goto outsideofColumnsLoop
End if
Next i
End If
Next Column

OutsideofColumnsLoop: 'Line Label