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.