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:
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.
thank you your content is helpful
Hello, Kim!
Thanks for your appreciation. For more useful content visit our site ExcelDemy.
Regards
ExcelDemy