Excel VBA

VBA Replace Function | How to Use Excel VBA Replace Function?

Excel VBA Replace Function

As in Excel, we have now a perform where we will discover and substitute any phrase or character or sentence with any letter. But by that process, we will only exchange one sort of sentence or letter at a time. With the assistance of VBA Replace Function, we will exchange as many phrases or letters or sentences in a single shot. This protects big effort and time doing single exercise multiple occasions. For this, we’ll use the Replace perform which is within the built-in VBA perform listing.

Under the syntax and argument of Replace perform in VBA.

Syntax of Replace

How to Use Excel VBA Replace Function?

We’ll find out how to use a VBA Replace Excel perform with few examples.

VBA Replace Function – Instance #1

We now have a sample knowledge of some sentences the place we’ll exchange one phrase and paste that up to date sentence in Sentence B Column.

Example 1-1

Comply with the under steps to use the Replace perform in Excel VBA.

Step 1: Go to VBA and in Insert menu tab, select Module.

VBA Replace Example 1-2

Step 2: Now write sub-category in the identify of a carried out perform or in any identify as per your selection as shown under.

Code:

Sub VBA_Replace2()

Finish Sub

VBA Replace Example 1-3

Step 3: Now define a dimension as Lengthy, as we shall be choosing an entire sentence. Right here we’ve got taken it as X.

Code:

Sub VBA_Replace2()

Dim X As Lengthy

Finish Sub

VBA Replace Example 1-4

Step four: Now in that Lengthy X, select the utmost vary till the place our knowledge might go up. Right here we’ve got taken as A1000 cell range and for going up till our knowledge starts use End(xlUp) followed by a dot(.)Row. Which suggests we can be shifting up in the identical column till that row which has the info. This course of in life Ctrl + Up arrow key in excel.

Code:

Sub VBA_Replace2()

Dim X As Lengthy
X = Vary(“A1000”).Finish(xlUp).Row

Finish Sub

VBA Replace Example 1-5

Word: We will select from End(xlDown) however it might not add any break or clean cell in between the info if knowledge has.

Step 5: Now again outline yet one more dimension Y as Long as shown under.

Code:

Sub VBA_Replace2()

Dim X As Long
X = Vary(“A1000”).Finish(xlUp).Row

Dim Y As Lengthy

Finish Sub

VBA Replace Example 1-6

Step 6: Now begin a For Next loop for second defined dimension Y from cell place 2 to until X range (last crammed cell)

Notice: Choosing cell 2 signifies that we aren’t considering the header here for exchange.

Code:

Sub VBA_Replace2()

Dim X As Lengthy
X = Range(“A1000”).Finish(xlUp).Row

Dim Y As Lengthy

For Y = 2 To X

Subsequent Y

End Sub

VBA Replace Example 1-7

Step 7: Now choose the Worth of Column B as Vary as Y followed by a dot(.) this is like inserting a perform in excel.

Code:

Sub VBA_Replace2()

Dim X As Long
X = Range(“A1000”).End(xlUp).Row

Dim Y As Lengthy

For Y = 2 To X

Range(“B” & Y).Value =

Subsequent Y

End Sub

VBA Replace Example 1-8

Step eight: Now as we now have seen in VBA syntax of Replace perform above now sort Replace and select Column A and Y as the first expression of string followed by the words which we’d like to exchange from the table.

Code:

Sub VBA_Replace2()

Dim X As Lengthy
X = Vary(“A1000”).Finish(xlUp).Row

Dim Y As Lengthy

For Y = 2 To X

Vary(“B” & Y).Worth = Replace(Vary(“A” & Y), “Delhi”, “New Delhi”)

Next Y

End Sub

VBA Replace Example 1-9

Step 9: Now compile the entire code and run. Once we do that we’ll the phrase “Delhi” from Sentence A is now changed with work “New Delhi” and the whole sentence is copied to Sentence B.

Result of Example 1

We will select a string of phrases or a sentence and substitute that with any required letters or sentence.

VBA Replace Function – Instance #2

There’s another method to exchange words or sentences with the help of VBA coding. For this, we’ve an inventory of seven subjects which are getting repeated. And we’d like to substitute these subject names with adding serial numbers earlier than each topic identify as proven under.

Example 2-1

As we will see in the above screenshot, column E has unique names of those subjects which are there in column B and column F has the subject names with a serial number initially of it.

Comply with under steps to use Replace perform in VBA.

Step 1: Now, go to VBA and from the Insert menu add new Module. Once we get it, begin writing the Sub-category within the identify of a perform which is being carried out as shown under.

Code:

Sub VBA_Replace()

End Sub

Sub-category Example 2-2

Step 2: Now think about a dimension Rng as Vary, InputRng as Vary and ReplaceRng as Vary. You possibly can choose another letters or phrases for defining ranges.

Code:

Sub VBA_Replace()

Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Vary

End Sub

VBA Replace Example 2-3

Step three: Now use xTitleId as dialog field and provides it a name. Here we’ve got named it “VBA_Replace“.

Code:

Sub VBA_Replace()

Dim Rng As Range
Dim InputRng As Vary, ReplaceRng As Vary

xTitleId = “VBA_Replace”

End Sub

VBA Replace Example 2-4

Step 4: Now assign Software.Choice with InputRng, it will enable the selected software to be used in VBA.

Code:

Sub VBA_Replace()

Dim Rng As Vary
Dim InputRng As Range, ReplaceRng As Vary

xTitleId = “VBA_Replace”
Set InputRng = Software.Choice

End Sub

VBA Replace Example 2-5

Step 5: Now in next line insert an InputBox of three varieties, Unique range, xTitleId, and InputRng. Unique Vary is an inventory of these topic which are wanted to get replaced, listed in column B. xTitledId is the dialog field names within the carried out perform. And InputRng is a variety of knowledge desk in column E and F.

Code:

Sub VBA_Replace()

Dim Rng As Vary
Dim InputRng As Range, ReplaceRng As Range

xTitleId = “VBA_Replace”
Set InputRng = Software.Choice
Set InputRng = Software.InputBox(“Unique Range “, xTitleId, InputRng.Tackle, Sort:=eight)

End Sub

VBA Replace Example 2-6

Step 6: Now in next line assign an Enter for ReplaceRng and for this select Replace Vary column. Remainder of it as similar.

Code:

Sub VBA_Replace()

Dim Rng As Vary
Dim InputRng As Vary, ReplaceRng As Vary

xTitleId = “VBA_Replace”
Set InputRng = Software.Choice
Set InputRng = Software.InputBox(“Unique Vary “, xTitleId, InputRng.Handle, Sort:=eight)
Set ReplaceRng = Software.InputBox(“Replace Range :”, xTitleId, Sort:=8)

End Sub

VBA Replace Example 2-7

Step 7: Now we’ll use Software.Screenupdating perform, because the identify says it’s used for updating if it is FALSE.

Code:

Sub VBA_Replace()

Dim Rng As Vary
Dim InputRng As Vary, ReplaceRng As Range

xTitleId = “VBA_Replace”
Set InputRng = Software.Choice
Set InputRng = Software.InputBox(“Unique Vary “, xTitleId, InputRng.Tackle, Sort:=8)
Set ReplaceRng = Software.InputBox(“Replace Range :”, xTitleId, Sort:=eight)
Software.ScreenUpdating = False

End Sub

VBA Replace Example 2-8

Step eight: Now insert a For-Next loop. And for each Rng vary, substitute the values from ReplaceRng column.

Code:

Sub VBA_Replace()

Dim Rng As Range
Dim InputRng As Vary, ReplaceRng As Vary

xTitleId = “VBA_Replace”
Set InputRng = Software.Selection
Set InputRng = Software.InputBox(“Unique Range “, xTitleId, InputRng.Handle, Sort:=eight)
Set ReplaceRng = Software.InputBox(“Replace Range :”, xTitleId, Sort:=eight)
Software.ScreenUpdating = False

For Every Rng In ReplaceRng.Columns(1).Cells

Subsequent

Finish Sub

Example 2-9

Step 9: Eventually exchange InputRng with the values current in Rng from the whole sheet.

Code:

Sub VBA_Replace()

Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Vary

xTitleId = “VBA_Replace”
Set InputRng = Software.Selection
Set InputRng = Software.InputBox(“Unique Vary “, xTitleId, InputRng.Tackle, Sort:=eight)
Set ReplaceRng = Software.InputBox(“Replace Range :”, xTitleId, Sort:=8)
Software.ScreenUpdating = False

For Every Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Worth, alternative:=Rng.Offset(0, 1).Value, Lookat:=xlWhole

Next

Finish Sub

Example 2-10

Step 10: Once accomplished, now compile and run the code. We’ll get and dialog box within the identify of VBA_Replace. From right here choose the record of topics which we’d like to substitute as Unique Range and click on on OK. Then we’ll get one other box from there select the Replace vary from E2 to F8. This desk has the info which need to get replaced and click on OK.

VBA Replace Example 2-11

We’ll get Knowledge in column B which can get replaced from the info in column F with serial number.

Result of Example 2

To an summary, column E has distinctive subject names. And column F has subject names with serial numbers. So by this knowledge in column B is replaced with knowledge in column F.

Execs of VBA Replace Function

  • We will substitute a number of words or sentences in a single shot.
  • There isn’t a limit of phrases or text what we can’t substitute with.
  • Syntax of Replace in VBA is as straightforward as utilizing SumIf perform in excel.
  • VBA Replace as shown in example-1 is the simplest method to apply.

Issues to Keep in mind

  • Save as Marco Enabled Excel to keep away from dropping written code in VBA.
  • All the time think about the size in such a means that it’ll create a value-added whereas we select these in code.
  • Be sure to select the whole range of sheet as shown in example-2 if it is restricted to get the end result. Or else you’ll be able to define and choose restricted cell range which would be used beneath Replace perform.

You’ll be able to obtain this VBA Replace Excel Template here – VBA Replace Excel Template

Really helpful Articles

This has been a guide to VBA Replace Function. Right here we mentioned VBA Replace and how to use Excel VBA Replace Function together with practical examples and downloadable excel template. You can even undergo our different steered articles –

  1. Full Tutorials on VBA Loops
  2. How to Use VBA Do While Loop?
  3. What’s VBA Quantity Format?
  4. How to create VBA MsgBox?

Excel VBA Course – All in One Bundle

120+ On-line Programs

500+ Hours

Verifiable Certificates

Lifetime Access

Study Extra

  • Excel Knowledge Evaluation Course

  • Excel for Advertising Course