CODEWITHSUNDEEP
vbaexcel
Boba Fett
Boba Fett

Reputation: 133

VBA - Find text and replace with adjacent column and repeat for each cell

Have another VBA loop question.

Couldn't quite figure out how to get the desired result with some of the various loop questions/answers I've seen here.

I have two columns. Column A contains the text that will be used to replace a specific portion of a URL in Column B (something like this: http://www.exampleurlwithTEXTIWANTTOREPLACE.com).

I can manage a single cell replacement:

.Range("B1").Replace "TEXTIWANTTOREPLACE", "TEXT2" + .Range("A1")

But I need to loop it...A1 needs to replace the text in B1. A2 needs to replace the text in B2. And so on until the loop reaches an empty cell in Column A.

Any help is much appreciated!

Upvotes: 0

Views: 3332

Answers (2)

David Zemens
David Zemens

Reputation: 53623

Define a range to represent your column, and a range variable to represent individual cell, then iterate over the cells within this column range, and do the replacement:

Dim colRange as Range
Dim cl as Range
Set colRange = Range("B1:B100") '# Modify as needed
For each cl in colRange.Cells
    cl.Replace "TEXTIWANTTOREPLACE", "TEXT2" & cl.Offset(0,-1).Value, xlPart
Next

Upvotes: 0

sous2817
sous2817

Reputation: 3960

If you're interested in a non-looping option, you could use:

Sub test()
    Sheet1.Range("B2:B25").Formula = "=SUBSTITUTE(A2,""A"",""B"")"
    Sheet1.Range("B2:B25").Value = Sheet1.Range("B2:B25").Value
End Sub

Replace the A2 with the cell you want to start your replacement, change the ""A"" with the text you're looking for, ""B"" with the text you want to replace it with. Be sure to leave the double quotes around your find and replace text. Adjust the range to suit your situation. Should be quite a bit faster than the looping option (depending on how large the range is).

Upvotes: 1

Related Questions