Loop Through Two Data Ranges at the Same Time?

Hi VBA Jedi Masters,

I would like to rewrite some inefficient VBA code. Suppose I have the following spreadsheet:

Diagram 1.jpg

I have a (overly complex) Macro which cycles through each princess in Column B. And for each princess, it prints a version of the poem in Column D, but with the "XXXXX" removed and the princess's name in its place:

Diagram 2.jpg

My VBA code is below. It works, and one of the things I like about it is that it is dynamic to the number of princesses. If you add or remove princesses, there will always be exactly one "text block" of the poem devoted to each listed princess. Here I'm defining "text block" as one set of "Mirror, mirror on the wall / Who is the fairest of them all? / That would be XXXXX" You get the idea.

But my VBA is inefficient. As a "Part One" of the code, it simply counts the number of princesses, then copies exactly one text block per princess into Column D. But then, as a "Part Two," it returns to the top of Column D and for each princess, it finds the next available "XXXXX" and REPLACE()s that princess's name in its place. Worse, it goes back to the top of Column D for each princess. So if I had 100 princesses, the Macro would have to scan Column D 100 times.

What would be ideal is if there was a way of doing Parts One and Two together, on-the-fly. In other words, as the Macro reads down the list of princesses, it generates one princess-customized text block in Column D as it goes.

Or, failing that, I'd be happy if I could just do Part Two in one pass, meaning read Column D just once and do the string substitution of "XXXXX" for a princess's name as I traverse.

Both of those solutions would require that I keep some kind of pointer or counter or iterator or something to Columns B and D. I've been fiddling with this all week, but my results are just awful and too painful to post here. Can anyone make any suggestions on how to approach this?

Many thanks!

My VBA:

Please Login or Register  to view this content.

Wow, thanks Mumps1! Its a pleasure to watch you in action.

Your code works great. I do have a question for you... Am I right that you are taking advantage of the fact that the "XXXXX" bit are the last five characters of the last string?

I should have been more explicit in my original post. In the toy spreadsheet I used as an example, this is great, but in my larger spreadsheet that I have at work, the "XXXXX" may appear at the beginning, middle, or end of a string; you can't know in advance. Would it be difficult to modify your code to use the

Please Login or Register  to view this content.

function? I took a stab and immediately got lost.

Thank you!

Thanks Richard,

This worked great. I'm a little confused how the SUBSTITUTE() function is working. Is there an advantage to using that over REPLACE() in this instance?

Thanks!!!