Find and Replace Text in Multiple Columns with Data from Two Other Column

[Bosca]

Hi All,

I have been trying to write VBA code that will replace text XXX with the text in cell A2, then A3, A4, etc. Also then replacing the text YYY with text from B2, then B3, B4, etc. across the range of cells from C to H.

I have a large amount of data so it will take a long time to do this over and over again.

Type	Serial number	Replacement Range 1	Replacement Range 2	Replacement Range 3	Replacement Range 4	Replacement Range 5	Replacement Range 6
Boeing	1111​	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel
Airbus	2222​	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel
Embraer	3333​	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel	XXX_YYY_Excel

Can someone assist with VBA code for this?

Thanks!

 

[Rick Rothstein]

Does this do what you want...

Sub Bosca()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:H" & LastRow) = Evaluate(Replace("SUBSTITUTE(SUBSTITUTE(C2:H#,""XXX"",A2:A#),""YYY"",B2:B#)", "#", LastRow))
End Sub

 

[Bosca]

Does this do what you want...

Sub Bosca()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:H" & LastRow) = Evaluate(Replace("SUBSTITUTE(SUBSTITUTE(C2:H#,""XXX"",A2:A#),""YYY"",B2:B#)", "#", LastRow))
End Sub

Thanks Rick, hasn't exactly worked. I left out the directory of the file name (didn't think it would matter here), but using formula text and then the VBA above it does what I want it to do.

Without the formulatext function it doesn't work exactly in my use case. It is running now and taking a while to run but this is because I'm accessing a direct link to over 300 other excel files.

Thanks for the above!