Excel Video 431 wraps up our discussion of looping with an example. We’re going to replace all of the underscore characters with spaces in a set of 6 cells. The first thing you’ll say is, “Nate, I can do that much easier with Find and Replace.” No question. To keep the example simple, we’ll just replace the underscore with a space, but with similar code you could do a custom find and replace. For example, every time you find something in cell C1, replace it with something in cell B1 but every time you find something in C2, replace it with what’s in B2, and so on.
There are two loops in the code. The For Each…Next loop will loop through each cell in our range of C1 to C6. While in each cell, the Do While…Loop checks to see if there’s still an underscore. If it finds an underscore, the code replaces the underscore with a space. If not, the code finishes the Do While…Loop and move to the next cell in the For Each…Next loop.
I hope the Excel Videos on looping have given you some helpful ideas. The next couple Excel Videos will use message boxes to allow your code to interact with the user. Thanks for watching.
