Nested Do While Loop InStr Function in Excel VBA

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:


The complete VBA code:
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

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 same. 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

Further reading:
Get in the loop with Excel macros

2 thoughts on “Nested Do While Loop InStr Function in Excel VBA

  1. Naval Kishore Meena

    i have created a userform for student search by enrolment no. or by name or by father’s name
    i want to results in list box. how can i do
    plz help thanks in advance
    Naval Kishore meena

    Reply
  2. jaime

    What happens when the info in columns D and E Changes…. It is not possible to list all elements of each of those columns in the VBA code. It should be a formula or code or that, Shouldn’t it?
    What will be the code for that?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *