Multiple Find and Replace

[Tigerexcel]

A common task in Excel is to update product codes, status etc. What is your preferred/quickest method to do this? I should explain that I'm talking multiple entries at the same time eg ABC to become ABC1, DEF to become DEF1 etc. The replacement information to change will vary and so hard-coding is not really an option.
I've seen VBA methods for doing this but my VBA skills are still basic and I can cope if there's not too much code and it's kept pretty simple. Is there perhaps another way using functions or some other means?

 

[My Aswer Is This]

Maybe something like this:
Put ABC in Range A1
Put ABC1 in Range B1
Put DEF in Range A2
Put DEF1 in Range B2

And on and on
The script will continue running as long as it sees Data in column A
Put the search for items in sheet named SearchItems
And have the script search for these items in sheet named Search
Or something like this.
If this would work let me know and give me specifics if my suggestion is not what would work for you.

 

[Tigerexcel]

Hi My Aswer, yes that would work ok, as long as the script is kept pretty basic. I would imagine you'd do this through a loop?

 

[My Aswer Is This]

Hi My Aswer, yes that would work ok, as long as the script is kept pretty basic. I would imagine you'd do this through a loop?

 

[My Aswer Is This]

So where on the sheet named Search will the script need to look? Or will it need to search the entire sheet.
Like can we tell it to search only columns A to G?
And when you say search for DEF you really mean just DEF correct?
Or do you mean it may be: "My new car is a DEF model"

 

[Tigerexcel]

So where on the sheet named Search will the script need to look? Or will it need to search the entire sheet.
Like can we tell it to search only columns A to G?
And when you say search for DEF you really mean just DEF correct?
Or do you mean it may be: "My new car is a DEF model"

Generally it the information to be replaced is within a particular column, the column can vary from month to month so let's say cols A to Z.
DEF is just DEF not part of a longer string.

 

[My Aswer Is This]

Try this:
The search for items should be in a sheet named SearchItems column A
The replacement item would be in column B of sheet named SearchItems.

The script will search the selection you choose no matter where it is.
So select the search Range and run the script.

Sub Replace_Me()
'Modified 6/27/2020 12:11:15 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim r As Range
Lastrow = Sheets("SearchItems").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
For Each r In Selection
If r.Value = Sheets("SearchItems").Cells(i, 1).Value Then r.Value = Sheets("SearchItems").Cells(i, "B").Value
Next
Next
Application.ScreenUpdating = True
End Sub

 

[Tigerexcel]

Thanks MAII nice.
Are there any non VBA alternatives?

 

[My Aswer Is This]

Thanks MAII nice.
Are there any non VBA alternatives?

You can use Excel Find/Replace function on your ribbon. But you would have to enter each value and replace value manually.
Your original post seemed to indicate you wanted a simple Vba code. Do you consider my code not simple?

 

[Tigerexcel]

You can use Excel Find/Replace function on your ribbon. But you would have to enter each value and replace value manually.
Your original post seemed to indicate you wanted a simple Vba code. Do you consider my code not simple?

Your code is fine but I will have to set this up for others whose Excel skills are not strong. I'm happy to go with the macro for myself and will most likely use it for others, just wondering what alternatives are there, if any.