Rifat Hassan
In this article, I’ll show you how you can use Excel VBA to find a specific text and replace it with another text within any range in a worksheet. You’ll learn to find a text and replace it with both case-sensitive and insensitive matches.
VBA Code to Find and Replace a Text in a Range in Excel (Quick View)
Sub Find_and_Replace_Case_Sensitive()
Find_Text = "Full"
Replace_Text = "Half"
Set Rng = Range("C4:C13")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
Next j
Next i
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Find and Replace a Text in a Range with Excel VBA
Here we’ve got a data set with some delivery dates and delivery items of a business company.
But after the schedule was made, it was revealed that the company doesn’t produce Full Sleeves. They will deliver Half Sleeves instead.
So we’ve to replace the term “Full” from the range with C4:C13 with the term “Half”.
We’ll accomplish this using Visual Basic of Applications (VBA) today.
1. Developing a Macro to Find and Replace a Text in a Range with Excel VBA (Case-Sensitive Match)
First of all, we’ll develop a Macro to find and replace a text within a range. We’ll use the case-sensitive match first.
The VBA code for this purpose will be:
⧭ VBA Code:
Sub Find_and_Replace_Case_Sensitive()
Find_Text = "Full"
Replace_Text = "Half"
Set Rng = Range("C4:C13")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
Next j
Next i
End Sub
⧭ Output:
Run the code. It’ll replace all the texts “Full” within the range C4:C13 of the active sheet with the text “Half”.
⧭ Notes:
Don’t forget to activate the worksheet with the data set before running the code. Also, change the find text, replace text, and range address according to your need before running the code.
Read More: Excel VBA: How to Find and Replace Text in Word Document
2. Creating a Macro to Find and Replace a Text in a Range with Excel VBA (Case-Insensitive Match)
This time, we’ll develop a Macro to find and replace a text within a range with a case-insensitive match.
The VBA code for this purpose will be:
⧭ VBA Code:
Sub Find_and_Replace_Case_Insensitive()
Find_Text = "full"
Replace_Text = "Half"
Set Rng = Range("C4:C13")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If UCase(Mid(Rng.Cells(i, j), k, Len(Find_Text))) = UCase(Find_Text) Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If UCase(Mid(Rng.Cells(i, j), k, Len(Find_Text))) = UCase(Find_Text) Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
Next j
Next i
End Sub
⧭ Output:
Run the code. Though we’ve used the text “full” inside our code, it’ll replace all the texts “Full” within the range C4:C13 of the active sheet with the text “Half”.
⧭ Notes:
Again don’t forget to activate the worksheet with the data set before running the code. Also, change the find text, replace text, and the range address according to your need before running the code.
Read More: Excel VBA: Open Word Document and Replace Text (6 Examples)
Similar Readings:
- How to Replace Text in Excel Formula (7 Easy Ways)
- How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
- How to Replace Text after Specific Character in Excel (3 Methods)
- How to Replace Text between Two Characters in Excel (3 Easy Ways)
3. Developing a UserForm to Find and Replace a Text in a Range with Excel VBA
Finally, we’ll develop a UserForm to find and replace a text within with Excel VBA.
⧪ Step 1: Opening the UserForm
Go to the Insert > UserForm option in the VBA editor to open a new UserForm. A new UserForm called UserForm1 will be opened.
⧪ Step 2: Dragging Tools to the UserForm
Besides the UserForm, you’ll get the Toolbox. Move your cursor over the Toolbox and drag 5 Labels and 2 ListBoxes (Under Label1 and Label5) and 3 TextBoxes (Under Label2, Label3, and Label4) in a way shown in the figure.
Finally, drag a CommandButton to the bottom right corner.
⧪ Step 3: Writing Code for ListBox1
Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Enter the following code there.
Private Sub ListBox1_Click()
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) = True Then
Worksheets(UserForm1.ListBox1.List(i)).Activate
Exit For
End If
Next i
On Error GoTo LB1
Range(UserForm1.TextBox1.Text).Select
Exit Sub
LB1:
x = 21
End Sub
⧪ Step 4: Writing Code for TextBox1
Then double click on TextBox1. Another Private Subprocedure called TextBox1_Change will open. Enter the following code there.
Private Sub TextBox1_Change()
On Error GoTo TB1:
ActiveSheet.Range(UserForm1.TextBox1.Text).Select
Exit Sub
TB1:
x = 21
End Sub
⧪ Step 6: Writing Code for CommandButton1
Finally, double click on CommandButton1. A Private Subprocedure called CommandButton1_Click will open. Enter the following code there.
Private Sub CommandButton1_Click()
Find_Text = UserForm1.TextBox2.Text
Replace_Text = UserForm1.TextBox3.Text
Set Rng = Range(UserForm1.TextBox1.Text)
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
If UserForm1.ListBox2.Selected(0) = True Then
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
End If
If UserForm1.ListBox2.Selected(1) = True Then
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If UCase(Mid(Rng.Cells(i, j), k, Len(Find_Text))) = UCase(Find_Text) Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If UCase(Mid(Rng.Cells(i, j), k, Len(Find_Text))) = UCase(Find_Text) Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
End If
Next j
Next i
Unload UserForm1
End Sub
⧪ Step 7: Writing Code for Running the UserForm
Insert a new Module from the VBA toolbar and insert the following code there.
Sub Run_UserForm()
UserForm1.Caption = "Find and Replace"
UserForm1.Label1.Caption = "Worksheet: "
UserForm1.Label2.Caption = "Range: "
UserForm1.Label3.Caption = "Find Text: "
UserForm1.Label4.Caption = "Replace with Text: "
UserForm1.Label5.Caption = "Match Type: "
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption
For i = 1 To Sheets.Count
UserForm1.ListBox1.AddItem Sheets(i).Name
Next i
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
UserForm1.ListBox1.Selected(i) = True
Exit For
End If
Next i
UserForm1.TextBox1.Text = Selection.Address
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.AddItem "Case-Sensitive"
UserForm1.ListBox2.AddItem "Case-Insensitive"
UserForm1.CommandButton1.Caption = "OK"
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 8: Running the UserForm (The Final Output)
Your UserForm is now ready to use. Select the data set (C4:C13) and run the Macro called Run_UserForm.
The UserForm will be loaded in the worksheet.
Change the worksheet name and cell reference if you want. Then enter the Find Text, Replace Text, and choose one between Case-Sensitive and Case-Insensitive.
So my UserForm looks like this:
Then click OK. You’ll get all the texts “Full” within the range C4:C13 with the text “Half”.
Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)
Things to Remember
We have used the UCase function of VBA to make a case-insensitive match here. It turns all the letters in a text to its upper case form.
Conclusion
So, these are the ways to find and replace a text within a range with the help of Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text with Blank Cell in Excel (5 Simple Methods)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
- Excel Formula to Replace Text with Number (5 Examples)
- How to Replace Text in Selected Cells in Excel (4 Simple Methods)
- Excel VBA: Replace Character in String by Position (4 Effective Ways)
Hello, am unable to download your example excel VBA File (NOT FOUND): Find and Replace with Excel VBA.xlsm
Is there another Link available for your artilce?
Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
Hello SHERRY,
This is Aung, one of the writers/developers in ExcelDmey. Thank you for pointing out the issue. We have re-uploaded the excel file. Now you can download it without any problem.
Good luck.