Macro - Replace cells if filter "ends with" criteria met

[gigioo7]

Hello Guys,

This is my first post here, i've spent the whole day searching through the forums and there are a lot of interesting things here, keep up the good work.

I have a worksheet with multiple columns. (Issue Date, Price, Qty, Product, Client Name etc) and about 11.000 rows.

I was trying to find a simple macro that will allow me to replace the cells (text) that end with ABC, on a single column - the client name, with SUBSCRIPTION.

So far i managed to adapt by replacing items one by one , but it's not very elegant.




sub ReplaceSUBSCRIPTION()

Cells.Replace What:="JOHNSON-ABC", Replacement:="SUBSCRIPTION", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="MARTIN-ABC", Replacement:="SUBSCRIPTION", LookAt:=xlPart, SearchOrder:=xlByRows

End Sub


How can it be done automatically?
Thank you!

 

[Gerald Higgins]

Hi, welcome to the board.

I'm not a macro expert, but maybe a wildcard character such as * would help.
For example

Cells.Replace What:="*-ABC", Replacement:...............

Certainly the standard Edit, Find and Replace functionality works this way outside macros.