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.

 

[Joe4]

A few questions:
- How many replacement values will you have?
- Are those replacements static (the same every time), or might you look for different values each time?
- Where is this list of values to find and what to replace them with found?
- Are you just searching column A, or a bigger range (if so, what is it), or the entire sheet?

 

[austinb]

Great questions, thank you.

A few questions:
- How many replacement values will you have? Answer: Varies each month
- Are those replacements static (the same every time), or might you look for different values each time? Answer: The specific replacement values will be static each month but which ones will be used will vary each month
- Where is this list of values to find and what to replace them with found? Answer: There won't be a list of values to find because they will vary each month
- Are you just searching column A, or a bigger range (if so, what is it), or the entire sheet? Answer: Will just be values in Column C

Here's some of the actual sample data.

UMB Transactions.xlsm
	C
2	#41 Ocean Prime Tampa
3	4 Wheel Parts # 247
4	5guys 0567 Qsr
5	76 - Panjabi Corp
6	76 - Quik Chek 76
7	76 - United Pacific 5624
8	7-Eleven 27438
9	7-Eleven 29938
10	8884156442logicmonitor
11	Ace Hardware 2
12	Advance Auto Parts #1058
13	Advance Auto Parts #8615
14	Advance Auto Parts #8866
15	Agent Fee
16	Alaska Air
17	Alberts Mexican Food 4
18	Allegnt*air Bnfdmx
19	Allegnt*air Bnz4d7
20	Allegnt*air Bphrlk
21	Amazon.Com*1b3r08u42 Amzn
22	Amazon.Com*1b79i17l2 Amzn
23	Amazon.Com*be7rg29h3 Amzn
24	Amazon.Com*u02yx5dz3 Amzn
25	Amazon.Com*y14d39vx3 Amzn
26	American Air
27	Amzn Mktp Us
28	Apple.Com/Bill
29	Applebees 808765180879
30	Applebees Ever11211331
31	Arbys 5009033
UMB Transactions

To give a little more context, the data is all of the credit card transactions for our company each month. So there might be a charge from McDonald's 12345, McDonald's 77753, McDonald's 00098. So right now I have to go in and either copy/paste "McDonald's" into each of those cells or delete the extraneous data from each cell. We have 30+ cardholders so the instances of similarly named vendors is quite high and manual changes are tedious and time consuming.

 

[Joe4]

To give a little more context, the data is all of the credit card transactions for our company each month. So there might be a charge from McDonald's 12345, McDonald's 77753, McDonald's 00098. So right now I have to go in and either copy/paste "McDonald's" into each of those cells or delete the extraneous data from each cell. We have 30+ cardholders so the instances of similarly named vendors is quite high and manual changes are tedious and time consuming.

To what end do you need to remove this extraneous information?
Are you trying to do some sort of calculation, and this extra information is causing issues (if so, there may be other ways around it without having the mess with the data)?

If we indeed go the VBA route to do this Search/Replace, since the values may change each month, how exactly do you envision communicating with the VBA code which values need to be replaced?
You don't plan on manually updating the VBA each month, do you?

 

[austinb]

To what end do you need to remove this extraneous information?
Are you trying to do some sort of calculation, and this extra information is causing issues (if so, there may be other ways around it without having the mess with the data)? Answer: To import the data into our accounting software. I already have code that provides the internalid based on the vendor name but it only recognizes an exact match.

If we indeed go the VBA route to do this Search/Replace, since the values may change each month, how exactly do you envision communicating with the VBA code which values need to be replaced? Answer: By using the find and replace message boxes that are detailed in the link from my original post. tl;dr Message box 1 brings up an input field of what to search for and then Message box 2 is to tell it what to replace it with.
You don't plan on manually updating the VBA each month, do you? Answer: Definitely not

Sample data after vendor name change

UMB Transactions.xlsm
	C		E
1	Supplier		Supplier Internal ID
2	Misc Credit Card Vendor		86079
3	Misc Credit Card Vendor		86079
4	Five Guys Burgers & Fries		74936
5	Misc Credit Card Fuel Vendor		75023
6	Misc Credit Card Fuel Vendor		75023
7	Misc Credit Card Fuel Vendor		75023
8	7-Eleven		74818
9	7-Eleven		74818
10	Logicmonitor Inc		95639
11	Ace Hardware		78966
12	Advance Auto Parts		74826
13	Advance Auto Parts		74826
14	Advance Auto Parts		74826
15	Agent Fee		1092174
16	Alaska Airlines		74830
17	Misc Credit Card Restaurant Vendor		75025
18	Allegiant Airlines		99343
19	Allegiant Airlines		99343
20	Allegiant Airlines		99343
21	Amazon.com		99408
22	Amazon.com		99408
23	Amazon.com		99408
24	Amazon.com		99408
25	Amazon.com		99408
26	American Airlines		74835
27	Amazon.com		99408
28	Apple		79003
29	Applebees		1316786
30	Applebees		1316786
31	Arby's		74838
UMB Transactions

Cell Formulas
Range	Formula
E2:E31	E2	=INDEX(AmEx_Template,MATCH([@Supplier],SearchTable,),1)

Named Ranges
Name	Refers To	Cells
AmEx_Template	='UMB Transactions'!$M$1:$M$4074	E2:E31
SearchTable	='UMB Transactions'!$N:$N	E2:E31

 

[Joe4]

So, you envision code that is going to prompt you for the values to Find/Replace each time?

You mention a list of 30 vendors.
Wouldn't it be better to either store that list on a sheet or in an array some where, and loop through, saying Find/Replace any value that begins with the value with just that value?

So, if "McDonalds" was on that list, it would replace anything that starts with "McDonalds..." with just "McDonalds".
Wouldn't that be better than being prompted 30 times, for each value you want to replace?

If so, do you prefer to store that in a list on a sheet, or in an array directly in VBA?

 

[austinb]

So, you envision code that is going to prompt you for the values to Find/Replace each time?

You mention a list of 30 vendors.
Wouldn't it be better to either store that list on a sheet or in an array some where, and loop through, saying Find/Replace any value that begins with the value with just that value?

So, if "McDonalds" was on that list, it would replace anything that starts with "McDonalds..." with just "McDonalds".
Wouldn't that be better than being prompted 30 times, for each value you want to replace?

If so, do you prefer to store that in a list on a sheet, or in an array directly in VBA?

Yes, that's what I was envisioning but I'm definitely open to a more efficient solution.

I actually already have all of the possible replacement values in column N so maybe provide me with the code and I can see if I can make it work?

 

[Joe4]

OK, see if this works for you:

Sub MyReplace()

    Dim lr As Long
    Dim r As Long
    Dim fnd As String
    
'   Find last row in column N with data
    lr = Cells(Rows.Count, "N").End(xlUp).Row
    
'   Loop all rows in column N, starting with row 2
    For r = 2 To lr
'       Get value to replace from column N
        fnd = Cells(r, "N")
'       Ignore "Not found" errors
        On Error Resume Next
'       Do all replacements
        Columns("C:C").Replace What:=fnd & "*", Replacement:=fnd, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'       Turn error messaging back on
        On Error GoTo 0
    Next r
    
    MsgBox "Replacements completed"
    
End Sub

I ran it on a sampling of values to replace in column N that look like this:

and it worked.

 

[austinb]

Looks like that works great for the cells that have the extraneous data, so thank you for that! However, it doesn't solve my issue for vendors such as:

American Air (s/b American Airlines)
Caseys ###### (s/b Casey's General Store)
Att (s/b AT&T)
Alaska Air (s/b Alaska Airlines)
etc

Suggestions for those instances?

 

[Joe4]

OK, you didn't mention that some of those replacements are going to be something entirely different (these are the important details that we need to know!)
There is no way that the code can "guess" what you want the replacement to be.

So I think you will need to make a second column, indicating what you want to replace it with, i.e.

Then, the code would be updated to:

Sub MyReplace()

    Dim lr As Long
    Dim r As Long
    Dim fnd As String
    Dim rpl As String
    
'   Find last row in column N with data
    lr = Cells(Rows.Count, "N").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
'   Loop all rows in column N, starting with row 2
    For r = 2 To lr
'       Get value to find from column N
        fnd = Cells(r, "N")
'       Get value to replace it with from column O
        rpl = Cells(r, "O")
'       Ignore "Not found" errors
        On Error Resume Next
'       Do all replacements
        Columns("C:C").Replace What:=fnd & "*", Replacement:=rpl, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'       Turn error messaging back on
        On Error GoTo 0
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Replacements completed"
    
End Sub