Find And Replace String Using VBA Code

There are several ways to replace string using VBA code,  by looping each cell or by using VBA Replace function.

Let's say that we want to replace "Macrosoft Excel" with "Microsoft Excel" from A1 through A500.

This first example loops from A1 through A500 and replace "Macrosoft Excel" with "Microsoft Excel".

Sub Find_Replace1()
Dim I As Integer
Dim SFind As String
Dim SReplace As String

SFind = "Macrosoft Excel"
SReplace = "Microsoft Excel"
For I = 1 To 500
If Cells(I, 1).Value = SFind Then
Cells(I, 1).Value = SReplace
End If
Next I
End Sub

The following example is more efficient than previous example:

Sub Find_Replace2()
    Dim SFind As String
Dim SReplace As String

SFind = "Macrosoft Excel"
SReplace = "Microsoft Excel"

Range("A1:A500").Replace _
What:=SFind, Replacement:=SReplace, _
LookAt:=xlWhole, MatchCase:=False
End Sub

If you to make the search case sensitive you can change the MatchCase property to true. And also if you want to replace data that contain part of the searched data you can change the LookAt property to xlPart.

Thank you for reading this article Find And Replace String Using VBA Code With URL http://x-tutorials.blogspot.com/2009/12/find-and-replace-string-using-vba-code.html. Also a time to read the other articles.