VBA Bulk Find and Replace or Similar

[TheCobbler]

Hi there,

I’m looking to do a large amount of find and replace on some data in two columns. (B:C)

I’ve been trying to do this in an efficient way, as I can tell the macro recorder doesn’t make this easy to read and becomes a bit convoluted if I do a basic find and replace on a selection.
How would you do this? I’ve been looking at loops and also arrays but am having a difficult time understanding which is best. (I’m relatively new to this!) I’d also like to store all the replacement values within the VBA code.

For example in column C, change “CRL48” to “Royal Mail”. This sort of change for around 15 examples. Once working this is something I’d like to apply to other sheets that may have up to 50 changes.

Thanks for reading, any help or advice greatly appreciated.

Cobb

 

[Rick Rothstein]

Does CRL48 fill the entire cell or is there other text in the cell with it? Are the letters CRL always upper case?

 

[TheCobbler]

Yes entire cell and yes uppercase - thanks!

 

[Rick Rothstein]

You can use Excel's Replace dialog box (CTRL+H) directly but, since you asked for a macro, here is that built-in function implemented in code...

Sub CRL48toRoyalMail()
  Dim FindMe As String, Replacement As String
  FindMe = "CRL48"
  Replacement = "Royal Mail"
  Columns("B:C").Replace FindMe, Replacement, xlWhole, , True, , False, False
End Sub

 

[TheCobbler]

You can use Excel's Replace dialog box (CTRL+H) directly but, since you asked for a macro, here is that built-in function implemented in code...

Sub CRL48toRoyalMail()
  Dim FindMe As String, Replacement As String
  FindMe = "CRL48"
  Replacement = "Royal Mail"
  Columns("B:C").Replace FindMe, Replacement, xlWhole, , True, , False, False
End Sub

Sub CRL48toRoyalMail()


Dim FindMe As String, Replacement As String

FindMe = "CRL48", “CRL24”

Replacement = "Royal Mail", “Royal Mail”

Columns("B:C").Replace FindMe, Replacement, xlWhole, , True, , False, False

End Sub

You can use Excel's Replace dialog box (CTRL+H) directly but, since you asked for a macro, here is that built-in function implemented in code...

Sub CRL48toRoyalMail()
  Dim FindMe As String, Replacement As String
  FindMe = "CRL48"
  Replacement = "Royal Mail"
  Columns("B:C").Replace FindMe, Replacement, xlWhole, , True, , False, False
End Sub

Great thanks for that. Would I able to add the different changes in a list as below?


Sub CRL48toRoyalMail()

Dim FindMe As String, Replacement As String

FindMe = "CRL48", “CRL24”, “TPN24”

Replacement = "Royal Mail", “Royal Mail”, “TRACKED”

Columns("B:C").Replace FindMe, Replacement, xlWhole, , True, , False, False

End Sub

 

[Rick Rothstein]

No, you will need to loop if you have a list...

Sub Replacements()
  Dim X As Long, FindThese As Variant, ReplaceWith As Variant
  FindThese = Array("CRL48", "CRL24", "TPN24")
  ReplaceWith = Array("Royal Mail", "Royal Mail", "TRACKED")
  For X = LBound(FindThese) To UBound(FindThese)
    Columns("B:C").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
  Next
End Sub

 

[TheCobbler]

Perfect - Thank you so much for your help. This is spot on!