Excel Macro Query - Replace cell contents | Excel forum

Hi Adrian,

Thank you for your post. You could use an IF function to change the appropriate cells according to your spec while leaving the rest unchanged.
Have a look at the sample macro I recorded below:

Sub Macro2()

'This macro runs on data in columns A and B of a worksheet.
Range("C1").Select
Range("C1") = "=IF(RC[-2]=""SIETCO"",""EPTBSIET"",RC[-1])"
'An IF-function which merges the content of A1 and B1 in C1
Selection.AutoFill Destination:=Range("C1:C2")
'You need to tell it how many rows to run it on ("C1: ")
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copies and pastes values from column C to column B
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft 'Deletes column C

End Sub

You can add nested IFs to handle more options.

Another option follows below:

Sub LoopChanges()

Range("B1").Select

Do Until ActiveCell.Offset(0, -1) = ""

If ActiveCell.Offset(0, -1) = "sietco" Then
ActiveCell = "eptbsiet"

End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Again, you can add ELSEIFs to handle more options. I have attached the Excel file (Replace Text) which contains these macros.

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer