Excel Macro Query - Replace cell contents

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