Tiger91
02-17-2016, 01:38 PM
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
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