Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to use Find and replace function in VBA

Today I'm going to explain you how to use another very important excel sheet function in VBA. It is find and replace function. If you are an excel user, surely you may have use this function manually. But today I'm going to explain you how to use this excel sheet function in a VBA program/macro.

In Excel 2013, you can find this function (Find & Select) under "Editing" section of "Home" Tab. If you are using any other version, you will able to find it in a similar place.

If you click on the arrow head, this kind of dropdown list will appear.

Then click on the "replace". Then you will get this pop up window.

You may have use this window before to find and replace words in your excel sheets. But today I'm going to explain you how to use this function in a VBA program. Let's use below sample paragraph to test our code.

So let's try to replace word "excel" with word "Word"

Following is the equivalent VBA code to do the "find and replace" worksheet function.

Sub FindReplace()

Dim SearchText As String
Dim ReplacementText As String

SearchText = "excel"
ReplacementText = "Word"

Worksheets("Sheet1").Cells.Replace What:=SearchText, Replacement:=ReplacementText, LookAt:=xlPart, MatchCase:=False

End Sub

You will get this result after running the above code.

However there is a one small problem with above code. Consider the following example.

If we run the macro for above sheet, You will notice that word "excellent" will change to "Wordlent".

Because of that, this find and replace code has limitations. If you need to avoid above type of errors, then you will need advance solution than this.

Contact Form


Email *

Message *

Popular Posts