Find and replace part of string in one cell with value of cell below it with VBA Macro

Question

I have multiple cells on one row in excel that all contain HTML. Each cell is in a different lang but has the same URLs throughout eg: (...loc=en-uk) I need to find and replace this in each cell but with a different value for each cell eg: find "en-uk" in cell A, Replace with "it-it", mover to next cell, find "en-uk", replace with "es-es" and so on till empty cell is reached.

Had tried the following but it only takes find and replace values from the same 2 cells:

Dim Findtext As String 
Dim Replacetext As String 

Findtext = Range("B2").Value 
Replacetext = Range("C2").Value 
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

upload your sheet's screenshot link and code if you tried any. — ZAT
Had tried the following but it only takes find and replace values from the same 2 cells: Dim Findtext As String Dim Replacetext As String Findtext = Range("B2").Value Replacetext = Range("C2").Value Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False — Danjayf
try to use proper formatting while posting code. And you can edit your question by adding code and hyperlinks of your sheet. — ZAT

peege peege · Accepted Answer · 2014-10-27T04:03:10

You will want to use Cells, instead of Range, and loop through the columns using a Variable "lCol". Range(Column Letter , Row Number) has it's limitations because you are using a letter. It's hard to add 1 to C.. C + 1 = D? So use Cells(row#, col#). That way, you can incrementally work your way through the columns or rows using numbers instead of letters. In this case, the variable is the column, so we will use lCol and loop from 1 to the Last Column Number. Replacing the text from an original source string using your original post as a starting point.

Note: I'm not sure where your original string was. Also, if you know what you are replacing from the original string, and find yourself looking at Row2 being all the same thing across the sheet, you can just set that in your replaceString statement below.

Try This - It's only a few lines, once you take out all the comments.

Sub TextReplace()

Dim sheetName As String
Dim findText As String
Dim replaceText As String
Dim lastCol As Long
Dim lCol As Long

'Set the sheetName here, so you don't have to change it multiple times later
sheetName = "Sheet1"

'Check the Last Column with a value in it in Row 3.  Assuming Row 3 has the Replacement text.
lastCol = Sheets(sheetName).Cells(3, Columns.Count).End(xlToLeft).Column

'Assuming you have an original string in a cell.  Range("A1"), in this case.
origString = Sheets(sheetName).Cells(1, 1).Value

'Loop through each Column - lCol is going to increase by 1 each time.  
'Starting on Column B,since Column A contains your original String.
For lCol = 2 To lastCol

'Using Cells(row#, col#), instead of Range(ColLetter, row#) gives you the ability to loop with lCol.
    findText = Sheets(sheetName).Cells(2, lCol).Value
    replaceText = Sheets(sheetName).Cells(3, lCol).Value

    'Put the value from Range("A1") with the text replaced from Row 2 with Row 3's value.
    Sheets(sheetName).Cells(1, lCol) = Replace(origString, findText, replaceText)

Next lCol

End Sub

Edit: Updated Column to start on B, instead of A.

Find and replace part of string in one cell with value of cell below it with VBA Macro

2 Answers