How to Find and Replace Using VBA (11 Ways)

If you are looking for some of the easiest ways to find and replace using VBA, then you are in the right place. In this article, you will get to know the easiest ways to find and replace your desired string using VBA in a data range easily.

Download Workbook

11 Ways to Find and Replace Using VBA

I have used the following table which has the records of results of some students. I will explain different ways to find and replace any data by using this table with the help of VBA.

For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Method-1: Finding String Without Optional Parameter

Here, I will find the student’s name marked by a red box, Daniel Defoe, in the Student Name column. To find the position of this student’s name in the data table you can follow this method.

Step-01:
➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

Sub SimpleFind()

Dim rng As Range

Set rng = Sheets("without optional parameter").UsedRange.Find("Daniel Defoe")

If Not rng Is Nothing Then

    MsgBox rng.Address

    MsgBox rng.Column

    MsgBox rng.Row

Else

    MsgBox "Not found"

End If

End Sub

Here, rng is declared as a range object, and “without optional parameter” is the name of the sheet.

After finding “Daniel Defoe” in the data range it will return the cell address, column number, and row number of this string in the Message Box.

In case the string is not matched up in the data range, then it will return “Not found”.

➤Press F5

Result:
After that, you will get the following Message Box containing the cell position of the student named “Daniel Defoe”.
➤Then Press OK.

Now, you will get the column position of this student.
➤Again Press OK.

Finally, you will get the row position of this student’s name.

Read more: How to Find String with VBA in Excel

Method-2: Finding Multiple Values With After Parameter

If you want to find the position of a student’s name like Michael James which is present in the table multiple times, then you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Write down the following code

Sub findingmultiplevalues()

Dim rng As Range, rng1 As Range, str As String

Set rng = Sheets("multiple values").UsedRange.Find("Michael James")

If rng Is Nothing Then Exit Sub

MsgBox rng.Address

Set rng1 = rng

str = str & "|" & rng.Address

Do

Set rng = Sheets("multiple values").UsedRange.Find("Michael James", After:=Range(rng1.Address))

    If InStr(str, rng.Address) Then Exit Do

    MsgBox rng.Address

    str = str & "|" & rng.Address

    Set rng1 = rng

Loop

End Sub

Here, rng and rng1 are declared as range objects and str as a string variable to store the address of the searched item.

“multiple values” is the sheet name and “Michael James” is the string that is to be found.

str = str & “|” & rng.Address will add the address to the string with a delimiter “|”.

Here, the DO loop will continue through the range to look for other instances and if the address is found for any instances then the loop will end.

➤Press F5

Result:
Afterward, you will get the following Message Box containing the cell position of the student named “Michael James”.
➤Then Press OK.

Now, you will get the second cell position of this student.
➤Again Press OK.

Finally, you will get the last cell position of this student.

Read more: How to Find String in a Cell Using VBA in Excel

Method-3: Finding String With LookIn Parameter

You can use the LookIn parameter in the VBA code to find your desired string.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub FindwithLookIn()

Dim rng As Range

Set rng = Sheets("LookIn").UsedRange.Find("Daniel Defoe", LookIn:=xlValues)

If Not rng Is Nothing Then

    MsgBox rng.Address

Else

    MsgBox "Not found"

End If

End Sub

“LookIn” is the name of the sheet and “Daniel Defoe” is the string which you are looking for.

Here, LookIn:=xlValues will give the final value of a cell after the calculation.

➤Press F5

Result:
Then, you will get the following Message Box containing the cell position of the student named “Daniel Defoe”.

Method-4: Finding String With Lookat Parameter

You can find the position of the student William David by using the Lookat Parameter in your VBA code.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub FindwithLookat()

Dim rng As Range

Set rng = Sheets("Lookat").UsedRange.Find("William", Lookat:=xlPart)

If Not rng Is Nothing Then

    MsgBox rng.Address

  Else

    MsgBox "Not found"

End If

End Sub

“Lookat” is the name of the sheet and “William” is the string which you are looking for.

Here, Lookat:=xlPart will give the position of the string if it matches partially or fully (so, I have written here only “William”)  but Lookat:=xlWhole will give the position of the string if it matches fully (in this case you have to write “William David”).

➤Press F5

Result:
Then, you will get the following Message Box containing the cell position of the student named “William David”.

Method-5: Finding String With SearchOrder Parameter

You can use the SearchOrder parameter to determine how the search will be carried out throughout the range to find the position of the student Michael James.

Step-01:
➤Follow Step-01 of Method-1
➤Write down the following code

Sub FindwithSearchOrder()

Dim rng As Range

Set rng = Sheets("SearchOrder").UsedRange.Find("Michael James", SearchOrder:=xlColumns)

If Not rng Is Nothing Then

    MsgBox rng.Address

Else

    MsgBox "Not found"

End If

End Sub

“SearchOrder” is the name of the sheet and “Michael James” is the string which you are looking for.

SearchOrder:=xlColumns will search for the value column by column and return the position of the string which comes first in the column-wise serial.

➤Press F5

Result:
Afterward, you will get the following Message Box containing the cell position of the student named “Michael James”.

You can try out the following code also for variance in results.
➤Write down the following code

Sub FindwithSearchOrder()

Dim rng As Range

Set rng = Sheets("SearchOrder").UsedRange.Find("Michael James", SearchOrder:=xlRows)

If Not rng Is Nothing Then

    MsgBox rng.Address

Else

    MsgBox "Not found"

End If

End Sub

SearchOrder:=xlRows will search for the value row by row and return the position of the string which comes first in the row-wise serial.

➤Press F5

Result:
Then, you will get the following Message Box containing the cell position of the teacher named “Michael James” (because the teacher’s name comes first in the row-wise direction).

Method-6: Finding String With SearchDirection Parameter

You can use the SearchDirection parameter to determine the search will be carried out throughout the range in which direction to find the position of the marked name of the student Michael James.

Step-01:
➤Follow Step-01 of Method-1
➤Write down the following code

Sub FindwithSearchDirection()

Dim rng As Range

Set rng = Sheets("SearchDirection").UsedRange.Find("Michael James", SearchDirection:=xlNext)

If Not rng Is Nothing Then

    MsgBox rng.Address

Else

    MsgBox "Not found"

End If

End Sub

“SearchDirection” is the name of the sheet and “Michael James” is the string which you are looking for.

SearchDirection:=xlNext will start the search in the top left-hand corner of the data range and search downwards, so it will give the position of the string which comes first.

➤Press F5

Result:
After that, you will get the following Message Box containing the cell position of the student named “Michael James” (serially first).

You can try out the following code also for variance in results.
➤Write down the following code

Sub FindwithSearchDirection()

Dim rng As Range

Set rng = Sheets("SearchDirection").UsedRange.Find("Michael James", SearchDirection:=xlPrevious)

If Not rng Is Nothing Then

    MsgBox rng.Address

Else

    MsgBox "Not found"

End If

End Sub

SearchDirection:=xlPrevious will start the search in the bottom right-hand corner of the data range and search upwards, so it will give the position of the string which comes last.

➤Press F5

Result:
Afterward, you will get the following Message Box containing the cell position of the student named “Michael James” (serially last).

Similar Readings:

• How to Find Exact Match Using VBA in Excel (5 Ways)
• VBA Find in Column in Excel (7 Approaches)

Method-7: Replacing String Without Optional Parameter

You can replace Donald Paul with Henry Jackson in the Student Name column by following this method easily.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub SimpleReplace()

Sheets("Simple Replace").UsedRange.Replace What:="Donald Paul", _ Replacement:="Henry Jackson"

End Sub

“Simple Replace” is the name of the sheet and “Donald Paul” is the string which you are looking for and “Henry Jackson” is the new name which you want to replace the previous one.

➤Press F5

Result:
Finally, you will get the new name Henry Jackson in the position of Donald Paul.

Method-8: Replacing String With REPLACE Function

You can simply use the REPLACE function for replacing any substring in a random string like this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub FindReplace()

MsgBox Replace("This is You What I am", "You", "Me")

End Sub

Here,  “You” will be replaced by “Me”

➤Press F5

Result:
Finally, you will get the Message Box containing the replacement in the string.

Read more: How to Find Substring Using VBA

Method-9: Find and Replace String for a Range of Data

I will replace Donald Paul with Henry Jackson in the Student Name column by using a VBA code in this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub FindandReplace()

Dim rng As Range

Dim str As String

With Worksheets("Find and Replace").Range("B5:B10")

Set rng = .Find("Donald Paul", LookIn:=xlValues)

    If Not rng Is Nothing Then

    str = rng.Address

    Do

    rng.Value = Replace(rng.Value, "Donald Paul", "Henry Jackson")

    Set rng = .FindNext(rng)

    Loop While Not rng Is Nothing

    End If

    End With

End Sub

Here, “Find and Replace” is the sheet name and “B5:B10” is the range of students’ names, and “Donald Paul” is the student’s name which is to be found out and then “Henry Jackson” will be the student’s name instead of the previous one.

WITH statement will avoid the repetition of the piece of code in every statement.

The IF statement will assign the item’s address to the str variable and the DO loop will replace all occurrences of the search word.

➤Press F5

Result:
Afterward, you will get the new name Henry Jackson in the position of Donald Paul.

Read more: Find within a Range with VBA in Excel: Including Exact and Partial Matches

Method-10: Find and Replace Multiple Strings Simultaneously

I will replace the names of three students Joseph Michael, Michael Anthony, and Donald Paul to Caroline Ceila, Katherine Anna, and Henry Jackson respectively at a time in this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub Multiplestrings()

Dim Sheet As Worksheet

Dim findlist As Variant

Dim repalcelist As Variant

Dim n As Long

Set Sheet = Sheets("Multiple Strings")

findlist = Array("Joseph Michael", "Michael Anthony", "Donald Paul")

replacelist = Array("Caroline Ceila", "Katherine Anna", "Henry Jackson")

  For n = LBound(findlist) To UBound(findlist)

          Sheet.Cells.Replace What:=findlist(n), Replacement:=replacelist(n), _

          LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, _

          SearchFormat:=True, ReplaceFormat:=True

  Next n

End Sub

Here, “Multiple Strings” is the sheet name, “Joseph Michael”, “Michael Anthony”, “Donald Paul” are the student’s names to be found and “Caroline Ceila”, “Katherine Anna”, “Henry Jackson” will replace the previous names respectively.

The FOR loop will perform all of the replacements here.

➤Press F5

Result:
After that, you will get the new name Caroline Ceila, Katherine Anna, and Henry Jackson.

Method-11: Find and Replace With Numbers of Cells Changed

In this method, I will replace the student’s name Donald Paul with Henry Jackson and count the number of replacements.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub CountingReplacedCells()

Dim Sheet As Worksheet

Dim fnd1 As Variant

Dim rplc1 As Variant

Dim Count As Long

fnd1 = "Donald Paul"

rplc1 = "Henry Jackson"

Set Sheet = Sheets("With Cell Numbers")

Count = Count + Application.WorksheetFunction.CountIf(Sheet.Cells, "*" & fnd1 & "*")

Sheet.Cells.replace what:=fnd1, Replacement:=rplc1, _

    LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, _

    SearchFormat:=True, ReplaceFormat:=True

MsgBox "I have made total replacements in " & Count & " cell(s)."

End Sub

Here, “With Cell Numbers” is the sheet name, “Donald Paul” is the name to be found, and “Henry Jackson” will replace the previous name.

Here, Count will store the number of how many times the replacement occurs.

➤Press F5

Result:
Afterward, you will get the new name Henry Jackson in the position of Donald Paul and a Message Box will show the total number of replacements which is in this case 2.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, I tried to cover the easiest ways to find and replace using VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Further Readings

• How to Use the Find Function in VBA (6 Examples)
• VBA Find Last Row in Excel (5 ways)
• FindNext Using VBA in Excel (2 Examples)