Method 1 – Use the Replace Method of VBA to Find and Replace Text in a Column
A. Build the Code
Open VBA Editor
- Press Alt+F11 on your keyboard to open the VBA editor.
- Select Insert>Module.
Create Subprocedure
Sub find_and_replace()
End SubThis is our subprocedure. We will write all the codes inside this.
Declare Necessary Variables
Sub find_and_replace()
Dim find_value, replace_value As String
End SubFind_value: This variable will contain the text that you to replace.
replace_value: This variable will contain the new text.
Take User Inputs
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
End SubWe used two input boxes that will take the inputs from the users and store them in the variables.
Replace the Text
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
Columns("C").replace What:=find_value, _
Replacement:=replace_value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End SubWe used the Replace method of VBA to find and replace the text.
Columns(“C”): We want to find and replace text from the Product column. See the Product column is the column C in our Excel sheet.
What:=find_value: The text you want to replace.
Replacement:=replace_value: Your new text value.
LookAt:=xlPart: It will look for the value and match it against your searched text.
SearchOrder:=xlByRows: It searches your text row by row in the column.
MatchCase:=False: It makes your search case-insensitive. It will match the text in both uppercase and lowercase.
Show Finished Message
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
Columns("C").replace What:=find_value, _
Replacement:=replace_value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Done With Replacement!"
End Sub
Finally, it will show you a finished message.
B. Run the Code
We have built the code. It’s time to check the code whether the code is working or not.
Press Alt+F8 to open the macro dialog box.
Select find_and_replace and click Run.
Provide the text you want to replace. Here, we want to replace the TV from the product column. After that, click OK.
Replace the TV with Television. Click OK.
See our VBA code successfully worked to find and replace the text from the column in the Excel sheet. See a message box after the replacement.
Method 2 – Use Loop to Find and Replace Text in a Column in Excel
A. Build the Code
Open VBA Editor
- Press Alt+F11 on your keyboard to open the VBA editor.
- Select Insert>Module.
Create Subprocedure
Sub find_and_replace_loop()
End SubThis is our subprocedure. Write all the codes inside this.
Declare Necessary Variables
Sub find_and_replace_loop()
Dim find_value, replace_value As String
End SubFind_value: This variable will contain the text that you want to replace.
replace_value: This variable will contain the new text.
Take User Inputs
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
End SubWe used two input boxes that will take the inputs from the users and store them in the variables.
On Error Resume Next: It is a method to handle errors. On Error Resume Next ignores the errors and proceeds to the next line.
Create Loop and Replace the Text in the Column
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
For Each rng In Range("C5:C18")
If rng.Value = find_value Then
rng.Value = replace_value
End If
Next
End SubFor Each rng In Range(“C5:C18”): We run a loop in the product column. We took the range of cells that have values.
If rng.Value = find_value: It checks whether the current cell value matches the user’s given value.
rng.Value = replace_value: If it matches, it sets the current cell value with the new value
Show Finished Message
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
For Each rng In Range("C5:C18")
If rng.Value = find_value Then
rng.Value = replace_value
End If
Next
MsgBox "Done With Replacement!"
End SubFinally, it will show you a finished message.
B. Run the Code
We have built the code. Check whether the code is working.
First, press Alt+F8 to open the macro dialog box.
Select find_and_replace_loop and click on Run.
Provide the text you want to replace. Replace the TV from the product column. Click OK.
Replace the TV with Television. Click OK.
Things to Remember
✎ We made the VBA Replace case-insensitive. You can set the value to True to find and replace the exact match.
✎ You can change the column according to your Excel sheet.
✎ One thing to remember, using the loop makes your code slightly slower. If you have a large dataset, it will be wise to use the Replace method instead of the loop.
Download Practice Workbook
Download this practice workbook.
Related Articles
- How to Find and Replace from List with Macro in Excel
- Excel VBA: How to Find and Replace Text in Word Document
- How to Find and Replace Multiple Values in Excel with VBA
- Find and Replace a Text in a Range with Excel VBA
- Excel VBA to Replace Blank Cells with Text
- Excel VBA: Replace Character in String by Position
