Excel VBA: Restart Loop Counter

Question

Hi, I am trying to restart my loop counter(iColumn). I am looping through the columns to replace multiple words in a template(TemplateSheet). Is it possible to restart the loop counter after looping through all the columns(inside loop).

My only problem is after incrementing the row value, it goes back to the loop(columns) then the value of iColumn becomes 4 and terminates the inside loop.

While Sheets("DataSheet").Cells(iRow, 1).Value <> ""

   While Sheets("DataSheet").Cells(1, iColumn) <> ""

      sFind = Sheets("DataSheet").Cells(1, iColumn)

      sReplacement = Sheets("DataSheet").Cells(iRow, iColumn)

      sTemplate = Replace(sTemplate, sFind, sReplacement)

      iColumn = iColumn + 1

Wend
      Sheets("OutputSheet").Cells(iRow, 1) = sTemplate
      iRow = iRow + 1
   Wend

The problem was solved in restarting the loop counter. But now I have to overwrite the replacement function because it doesn't store the new replaced data.

What if you add iColumn = 1 after iRow = iRow + 1? Also, should the definition of sFind not go right before entering the inner loop? — Ioannis
– Ioannis, Commented Jun 12, 2015 at 14:32
Thank you so much @loannis! But I still have another problem. I have to overwrite the replacement function. Because it doesn't store the next new replaced data. — bigbryan
– bigbryan, Commented Jun 12, 2015 at 14:39
Could you add to the question what is that you trying to achieve in addition to why your approach does not work? You can get better help this way.. I sense that what you are trying to do can be done in a far easier way. See this. If FreeMan's answer has solved your original issue, it would be nice if you accept it/upvote it. — Ioannis
– Ioannis, Commented Jun 12, 2015 at 14:44
Can you give an example of what you are trying to find/replace? It looks like you are looking in the rows below for a string that's in your first column. Then you are replacing that entire cell with the text from the first row. Is that correct? — Tony L.
– Tony L., Commented Jun 12, 2015 at 14:49
I agree with @Ioannis - if you let us know what you're trying to achieve, we might be able to help you find a better solution. especially after Tony L pointed out that this is the 2nd nearly identical question you've accepted an answer for. — FreeMan
– FreeMan, Commented Jun 12, 2015 at 14:57

FreeMan · Accepted Answer · 2015-06-12 14:33:08Z

2

Simply reset the value of iColumn to whatever your initial value needs to be. I assumed 1.

While Sheets("DataSheet").Cells(iRow, 1).Value <> ""
   While Sheets("DataSheet").Cells(1, iColumn) <> ""
      sFind = Sheets("DataSheet").Cells(1, iColumn)
      sReplacement = Sheets("DataSheet").Cells(iRow, iColumn)
      sTemplate = Replace(sTemplate, sFind, sReplacement)
      iColumn = iColumn + 1
   Wend
   MsgBox sTemplate
   iRow = iRow + 1
   iColumn = 1
Wend

You can simplify your code a bit like this:

While Sheets("DataSheet").Cells(iRow, 1).Value <> ""
   While Sheets("DataSheet").Cells(1, iColumn) <> ""
      sTemplate = Replace(sTemplate, Sheets("DataSheet").Cells(1, iColumn), Sheets("DataSheet").Cells(iRow, iColumn))
      iColumn = iColumn + 1
   Wend
   MsgBox sTemplate
   iRow = iRow + 1
   iColumn = 1
Wend

Finally, note that the location of your MsgBox call you will only get the final value of sTemplate, not any of the intermediate values. That may, of course, be what you're after.

answered Jun 12, 2015 at 14:33

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony L. Over a year ago

@bigbryan Looks like I gave you a similar answer on the loop the other day. Did that not fix your loop? stackoverflow.com/a/30673171/3347858

bigbryan Over a year ago

Yes sir @TonyL . Thank you Sir. But I still have one problem. I have three sheets of data: DataSheet, TemplateSheet, OutputSheet. I am trying to loop through every column(DataSheet) to find the codename to be replaced(the same codename on the TemplateSheet). I will get the replacement words from "DataSheet" . Then I will store the new template in the "OutputSheet". Now my problem is, I don't know how to overwrite the replacement function for the new template. The output stores the same data of template from the first loop and it never replaces, Thank you!

FreeMan Over a year ago

That should probably have been your question in the first place: I'm trying to do "X". Here's what I've done so far. Here's the problem I'm running into.

Tony L. · Accepted Answer · 2015-06-12 15:48:12Z

1

If sTemplate has the value that you want in the cell, then you'll need to set the cell to that data as well like so:

Sheets("DataSheet").Cells(iRow, iColumn) = sTemplate

Here's the whole loop:

While Sheets("DataSheet").Cells(iRow, 1).Value <> ""
    While Sheets("DataSheet").Cells(1, iColumn) <> ""
        sFind = Sheets("DataSheet").Cells(1, iColumn)
        sReplacement = Sheets("DataSheet").Cells(iRow, iColumn)
        sTemplate = Sheets("TemplateSheet").Cells(1, 1)
        Sheets("OutputSheet").Cells(iRow, iColumn) = Replace(sReplacement, sTemplate, sFind)
        iColumn = iColumn + 1
    Wend
    MsgBox sTemplate
    iRow = iRow + 1
    iColumn = 1
Wend

edited Jun 12, 2015 at 15:48

answered Jun 12, 2015 at 15:04

Tony L.

19.7k8 gold badges72 silver badges68 bronze badges

2 Comments

Tony L. Over a year ago

@bigbryan Does sTemplate currently get the correct value you want in the OutputSheet?

Tony L. Over a year ago

@bigbryan, I also changed the order of the parameters in the Replace function after making some assumptions.

Collectives™ on Stack Overflow

Excel VBA: Restart Loop Counter

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related