Do Until IsEmpty to loop through multiple user-defined ranges

Question

This is a continuation of the question Do Until IsEmpty to loop through the user-defined ranges:

In the code below the excel Macro loops through Original text range, replacing all the instances of "tagname" with the assigned loopText in cell D2 and puts the corrected text into cell B9. Then it checks whether the looptext range has other values and loops through the original text again to account for these. Once the looptext cell is empty, the macro stops and prints the corrected text for each instance of loopText. Parallel to replacing "tagname" in cells D2 and onward I want the Macro to replace the word "sheetname" with the range of cells in E2 and onward. I have created a separate Do Until IsEmpty loop, however excel only executes the first one.

Please, help locate an error in my VB code.

Thank you.

Sub CommandButton21_Click()
Dim correctedText2 As Range
Dim OriginalText2 As Range
Dim loopText1 As Range
Dim loopText2 As Range
Dim cel As Range
Dim i As Long
Dim j As Long
Dim k As Long

Set OriginalText2 = Range("H3:H20")
Set correctedText2 = Range("B9")
Set loopText1 = Range("D2")
Set loopText2 = Range("E2")

i = 0

j = 0

Do Until IsEmpty(loopText1.Offset(j).Value)

    For Each cel In OriginalText2
        correctedText2.Offset(i).Value = Replace(cel.Value, "tagname", loopText1.Offset(j).Value)
        i = i + 1
    Next cel
    j = j + 1
Loop

k = 0

Do Until IsEmpty(loopText2.Offset(k).Value)

    For Each cel In OriginalText2
        correctedText2.Offset(i).Value = Replace(cel.Value, "sheetname", loopText2.Offset(k).Value)
        i = i + 1
    Next cel
    k = k + 1
Loop

End Sub

Check the value of the loopText2.Offset(k).Value before start of the second loop — Harsh
Don't understand what you're doing but are you sure you want to use correctedText2 in both loops? — findwindow
@findwindow I believe that could be causing the problem, only the first loop is executed. However, correctedText is the same modification of originalText. I am just trying to modify originalText by replacing the words "tagname" and "sheetname" with cell ranges loopText1 and loopText2 respectively. — ksenia
I still don't get it XD I need to see it :/ I would also take a look at what @Harsh said. — findwindow
Yes!!! @ScottHoltzman you got it! Spot on! Works like magic. Thanks! — ksenia

ksenia ksenia · Accepted Answer · 2015-11-25T15:56:15

Thanks to @ScottHoltzman, here's the answer!

Sub CommandButton21_Click()
Dim correctedText1 As Range
Dim correctedText2 As Range
Dim correctedText3 As Range
Dim OriginalText1 As Range
Dim OriginalText2 As Range
Dim loopText1 As Range
Dim loopText2 As Range
Dim cel As Range
Dim i As Long
Dim j As Long

Set OriginalText2 = Range("H3:H20")
Set correctedText2 = Range("B9")
Set loopText1 = Range("D2")
Set loopText2 = Range("E2")

i = 0
j = 0

Do Until IsEmpty(loopText1.Offset(j).Value)

    For Each cel In OriginalText2
        correctedText2.Offset(i).Value = Replace(Replace(cel.Value, "tagname", loopText1.Offset(j).Value), "sheetname", loopText2.Offset(j).Value)
        i = i + 1
    Next cel
    j = j + 1

Loop

End Sub

Instead of creating two Do Until IsEmpty Loops, combine into one loop with a double replace.

Do Until IsEmpty to loop through multiple user-defined ranges

1 Answers