• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro: Replace text starting and ending with values stored in variables

P

Priyanka

New Member
Hi,

I have a variable x and a variable y. Now i want to search and replace text falling between x and y in the overall workbook.

Now I am able to use Replace What with just 1 variable.

..........Replace What:=x , Replacement:="New Text"............

I can also succesfully do Replace What with wild card searches with specific text strings
..........Replace What:="<cs1>*</cs1>", , Replacement:="New Text"............

But its wild cards with variables where I am stuck. I would want to do something like :

..........Replace What:=x *y, Replacement:="New Text"............
 
So if original text was:
This is x kill this y is good
It should become this, correct?
This is x new text y is good

If so, command is:
Replace What:=x & "*" & y , Replacement:=x & "New Text" & y

If x and y themselves should also be eliminated, making example sentence:
This is new text is good

Command should be:
Replace What:=x & "*" & y , Replacement:="New Text"
 
Hi!!

thankyou for responding!

I tried what you gave here. Works fine. The only problem is that in the code above i want to get rid of x and y and just retain the text lying between them.

Say my text is <B>Bold</B>, i would like to just keep Bold.
Do I make a sense here?
 
Perhaps like this then?
Code: 
Sub Example()
Dim myRange As Range
'Assumes value is in this range
Set myRange = Range("A1:A10")
myRange.Replace "*<B>", ""
myRange.Replace "</B>*", ""

End Sub
 
You must log in or register to reply here.
Back
Top