Using VBA to Find and Replace All Data in a Cell

[austinb]

Hello All,

I'd like to piggyback off of this question (VBA using input boxes to find and replace) and add a follow-up question. Instead of simply replacing the found text, I'd like to be able to completely overwrite the cell data with the replace data.

Book1
	A
1	Apple 3456
2	Apple 1234
3	Orange 1234
4	Orange 3456
Sheet1

So if I ran a Find for Apple and a Replace to Orange, it would change cells A1 & A2 to just "Orange" (no numbers)
Similarly, if I ran a Find for 1234 and a Replace to Lemon, it would change cells A2 & A3 to just "Lemon"

Thank you for any advice.

 

[austinb]

I think that's why I was going with the Find and Replace option originally. I've tried to make this process more efficient in the past but have been too busy for the last few months to work on it.

I don't think the new code would work because the vendor names can vary so much. For example, this current month has all of the following names which should be changed to Casey's General Store:

Caseys #2080
Caseys #2587
Caseys #2605
Caseys #3272
Caseys #3272
Caseys #3272
Caseys #3272
Caseys #3711
Caseys #3835

or Allegiant Airlines:

Allegnt*air Bnfdmx
Allegnt*air Bnz4d7
Allegnt*air Bphrlk


To reiterate, my original thought was to do a find and replace for say, "Caseys" and for every instance of that found variable, the entire cell's contents would then be replaced with "Casey's General Store". I fully understand VBA cannot guess what I want it to do.

So maybe the best option would be to add the first iteration of your code and then run this second step for the remaining ones that need correcting. However, it still leaves me with the question, is there some code that can either:

1) replace the original cell contents with user inputted replacement text if certain text is found within that cell
2) enter the replacement text in an adjacent cell

Sorry if I'm not providing enough data or making this more difficult than it should be and again, thank you very much for your help and efforts!

 

[Joe4]

I don't think the new code would work because the vendor names can vary so much. For example, this current month has all of the following names which should be changed to Casey's General Store:

Caseys #2080
Caseys #2587
Caseys #2605
Caseys #3272
Caseys #3272
Caseys #3272
Caseys #3272
Caseys #3711
Caseys #3835

or Allegiant Airlines:

Allegnt*air Bnfdmx
Allegnt*air Bnz4d7
Allegnt*air Bphrlk

I think you misunderstood how my code works. You do NOT need to list each one separately. Just list the common prefix.

So for all your "Caseys..." entries, you only need one line:

Value to find in column N: "Caseys"
Value to replace in column O: "Casey's General Store"

The reason it works is because I am adding a wildcard to the end of the Find value (perhaps you missed that):

Columns("C:C").Replace What:=fnd & "*",...

So basically, what it is saying (in this example) is Find and Replace any entry that STARTS WITH "Caseys" (it does NOT need to equal it exactly).

 

[austinb]

OK, thank you. It's going to take a bit of time to get the data in order because 3 other columns rely on that same data for their internalid's so I think I'll need to mess with it some. Thank you for your patience with me!

 

[Joe4]

You are welcome.
Feel free to post back to this thread if you run into any issues with this particular question.

I hope you see why I was asking all those questions. The code is so dependent upon all those little details.
Hopefully, it kind of gives you an idea of the level of information that needs to be provided in order to get solutions that will really work on your data, and you will have a better idea of what to think about and include in future questions.

Happy Excel-ling!

 

[austinb]

OK, I got it to work now. Thank you again so much!

 

[Joe4]

You are welcome.
I am actually going to re-mark the solution as the post that contain the last updated code, as the post you marked isn't anything new, just explaining what the code does.
We typically want to mark the post that contains the solution.