How do I replace a value in Excel VBA?

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 r

How do I replace a value in Excel VBA?

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.Table of Contents hideDownload Workbook11 Ways to Find and Replace Using VBAMethod-1: Finding String Without Optional ParameterMethod-2: Finding Multiple Values With After ParameterMethod-3: Finding String With LookIn ParameterMethod-4: Finding String With Lookat ParameterMethod-5: Finding String With SearchOrder ParameterMethod-6: Finding String With SearchDirection ParameterMethod-7: Replacing String Without Optional ParameterMethod-8: Replacing String With REPLACE FunctionMethod-9: Find and Replace String for a Range of DataMethod-10: Find and Replace Multiple Strings SimultaneouslyMethod-11: Find and Replace With Numbers of Cells ChangedPractice SectionConclusionFurther Readings

Download WorkbookVBA Find and Replace.xlsm

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.

VBA find and replace

Method-1: Finding String Without Optional Parameter

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

VBA find and replace

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

finding without optional parameter

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

finding without optional parameter

After that, a Module will be created.

finding without optional parameter

Step-02:
Write the following codeSub 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.

finding without optional parameter

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.

VBA find and replace

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

finding without optional parameter

Finally, you will get the row position of this students name.

finding without optional parameter

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 students name like Michael James which is present in the table multiple times, then you can follow this method.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Write down the following codeSub 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.

finding multiple values

Press F5

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

finding multiple values

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

finding multiple values

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

VBA find and replace

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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.

LookIn Parameter

Press F5

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

LookIn Parameter

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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).

Lookat Parameter

Press F5

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

VBA find and replace

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Write down the following codeSub 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.

SearchOrder Parameter

Press F5

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

SearchOrder Parameter

You can try out the following code also for variance in results.
Write down the following codeSub 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.

SearchOrder Parameter

Press F5

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

SearchOrder Parameter

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Write down the following codeSub 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.

SearchDirection Parameter

Press F5

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

SearchDirection Parameter

You can try out the following code also for variance in results.
Write down the following codeSub 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.

SearchDirection Parameter

Press F5

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

VBA find and replace

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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.

simple replace

Press F5

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

simple replace

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 codeSub FindReplace() MsgBox Replace("This is You What I am", "You", "Me") End Sub

Here, You will be replaced by Me

simple replace

Press F5

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

VBA find and replace

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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 students name which is to be found out and then Henry Jackson will be the students 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 items address to the str variable and the DO loop will replace all occurrences of the search word.

find and replace

Press F5

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

find and replace

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.

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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 students 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.

multiple strings

Press F5

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

multiple strings

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

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

VBA find and replace

Step-01:
Follow Step-01 of Method-1
Type the following codeSub 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.

with cell numbers

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.

with cell numbers

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.

practice

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)

Video liên quan