Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)

Get FREE Advanced Excel Exercises with Solutions!

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

Here we’ve got a data set with some delivery dates and delivery items of a business company.

Data Set to Find and Replace a Text in a Range with Excel VBA

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 Macro to Find and Replace 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

VBA Code to Find and Replace in a Range in Excel

⧭ 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.


2. Creating Macro to Find and Replace 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

VBA Code to Find and Replace a Text in a Range with Excel VBA

⧭ 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.


3. Developing 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.

Inserting UserForm to Find and Replace a Text in a Range with Excel VBA

⧪ 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.

Dragging Tools to Find and Replace a Text in a Range with Excel VBA

⧪ 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

ListBox1 Code to Find and Replace a Text in a Range with Excel VBA

⧪ 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

CommandButton1 Code to Find and Replace a Text in a Range with Excel VBA

⧪ 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

UserForm Code to Find and Replace a Text in a Range with Excel VBA

⧪ 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:

Loading UserForm to Find and Replace a Text in a Range with Excel VBA

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


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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo