Interesting Solution Using A Nested Do While Loop Macro and InStr function

One of our students saw this challenge on the Internet. You had many phrases in a column. In another column you had keywords. Next to these keywords were placed names of people. Now the keywords had to be extracted from the phrase, compared to the keyword shown and assign the correct name to the phrase. Indeed a very useful exercise. Vlookup and nested if functions could not solve the problem but our macro running a nested do while loop solved the problem quite elegantly. The code is placed here for your ready reference:

Sub find_color()
x = 2
y = 2
Do While Cells(x, 1) <> ""
MyText = Cells(x, 1).Value
Do While Cells(y, 4) <> "" If InStr(MyText, "blue") And Cells(y, 4) = "blue" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "brown") And Cells(y, 4) = "brown" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "lavendar") And Cells(y, 4) = "lavendar" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "magenta") And Cells(y, 4) = "magenta" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "orange") And Cells(y, 4) = "orange" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "yellow") And Cells(y, 4) = "yellow" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "pink") And Cells(y, 4) = "pink" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "white") And Cells(y, 4) = "white" Then Cells(x, 2) = Cells(y, 5)
If InStr(MyText, "red") And Cells(y, 4) = "red" Then Cells(x, 2) = Cells(y, 5)
y = y + 1
Loop
x = x + 1
y = 2
Loop
End Sub
What does the macro do?

First it initializes the rows for the outer and inner loops where they will start.
Next it runs the outer loop which places a kind of counter on the first keyword.
Then the phrase is assigned to a variable called MyText which can hold a string or sentence.
Now the 'InStr' text function extracts the keyword from the phrase and compares it with the shown keyword.
If the extracted keyword and the shown keyword match, it assigns the a name to the phrase.
If the keywords don't match it goes to the next phrase and does the name. When it encounters an empty row, the inner loop jumps to the outer loop, picks up another keyword and again runs the comparison inner loop and so on.
In this manner the names are assigned correctly.

Home