Clever Geek Handbook

Excel VBA: restart cycle counter

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

My only problem is that after increasing the row value, it goes back to the loop (columns), then the iColumn value becomes 4 and ends the inner 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 issue was resolved when restarting the cycle counter. But now I need to overwrite the replace function because it does not store the new replaced data.

+3


source to share


2 answers


Just reset the value iColumn

should match your initial value. I assumed that 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:



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 at the point of your call, MsgBox

you will receive the final value sTemplate

, not any intermediate value. This may, of course, be what you need.

+2


source


If the sTemplate has the value you want in the cell, then you will need to set the cell for that data the same way:

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
+1


source






More articles:


All Articles