HomeInteresting 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) 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
