InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole Step 9: At last, replace InputRng with the values present in Rng from the whole sheet. And for each Rng range, replace the values from ReplaceRng column.Īpplication.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells Step 7: Now, we will use the Application.screenupdating function, as the name says it is used for updating if it is FALSE.Īpplication.ScreenUpdating = False End Sub Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Step 6: Now, in the next line, assign an Input for ReplaceRng and for this, select Replace Range column. Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) And InputRng is a range of data table in column E and F. xTitledId is the dialog box names in the performed function. Original Range is a list of those subject which are needed to get replaced, listed in column B. Step 5: Now, in the next line, insert an InputBox of 3 types, Original range, xTitleId, and InputRng. Step 4: Now assign the Application.Selection with InputRng, this will enable the selected application to be used in VBA. Step 3: Now, use xTitleId as dialog box and give it a name. You can choose any other letters or words for defining ranges.ĭim InputRng As Range, ReplaceRng As Range Step 2: Now consider a dimension Rng as Range, InputRng as Range and ReplaceRng as Range. Once we get it, start writing the Sub-category in the name of a function which is being performed, as shown below. Step 1: Now, go to VBA and from the Insert menu, add a new Module. And we need to replace those subject names with adding serial numbers before each subject name, as shown below.Īs we can 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 at the start of it.įollow the below steps to use Replace function in VBA. For this, we have a list of 7 subjects which are getting repeated. There is another way to replace words or sentences with the help of VBA coding. We can select a string of words or a sentence and replace that with any required letters or sentence.
Once we do that, we will the word “Delhi” from Sentence A is now replaced with work “New Delhi”, and the whole sentence is copied to Sentence B. Step 9: Now, compile the complete code and run. Range("B" & Y).Value = Replace(Range("A" & Y), "Delhi", "New Delhi")
Step 8: Now, as we have seen in the VBA syntax of Replace function above now type Replace and select Column A and Y as the first expression of string followed by the words which we need to replace from the table. Step 7: Now select the Value of Column B as Range as Y followed by a dot(.) this is like inserting a function in excel. Note: Selecting cell 2 means that we are not considering the header here for replace.
0 kommentar(er)
