Macro that searches for keyword and replaces it with ...
 

Here is what i need from a macro : i have an excel sheet . In A column i
have book titles . In B column i have their descriptions . From time to
time the description contains the keyword "This title" . I need the
macro to find the cells in B column where this keyword is and to
replace it with the text from cell corresponding from A column and put
the new description in C column . Here i have an example :

A1: "Once upon a time" B1: This title is the best book ....
A2: "The killer" B2: John Big wrote this title when he
was....


The result should be :

C1 : "Once upon a time" is the best book...
C2 : John Big wrote "The Killer" when he was...


Can this be done ? Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz

Macro that searches for keyword and replaces it with ...
 

You can give this a try. Expand the range for column A as needed.

Sub stitute()
Dim c As Range, myText As String
For Each c In Range("A2:A4")
myText = c.Offset(0, 1).Value
myText = Replace(myText, "this title", _
c.Value, , , vbTextCompare)
c.Offset(0, 2) = myText
Next
End sub



"andrei" wrote in message
...

Here is what i need from a macro : i have an excel sheet . In A column i
have book titles . In B column i have their descriptions . From time to
time the description contains the keyword "This title" . I need the
macro to find the cells in B column where this keyword is and to
replace it with the text from cell corresponding from A column and put
the new description in C column . Here i have an example :

A1: "Once upon a time" B1: This title is the best book ....
A2: "The killer" B2: John Big wrote this title when he
was....


The result should be :

C1 : "Once upon a time" is the best book...
C2 : John Big wrote "The Killer" when he was...


Can this be done ? Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz

Macro that searches for keyword and replaces it with ...
 

Hello Andrei,


enter into C1
=Substitute(B1,"this title",A1,1)

(Pull formula down in column C)
Best Regards,

Gabor Sebo
"andrei" wrote in message
...

Here is what i need from a macro : i have an excel sheet . In A column i
have book titles . In B column i have their descriptions . From time to
time the description contains the keyword "This title" . I need the
macro to find the cells in B column where this keyword is and to
replace it with the text from cell corresponding from A column and put
the new description in C column . Here i have an example :

A1: "Once upon a time" B1: This title is the best book ....
A2: "The killer" B2: John Big wrote this title when he
was....


The result should be :

C1 : "Once upon a time" is the best book...
C2 : John Big wrote "The Killer" when he was...


Can this be done ? Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz

Macro that searches for keyword and replaces it with ...
 

This can actually be done in a shorter command.

Sub chngTtl()
Dim c As Range
For Each c In Range("A2:A4") '<<<adjust range to needs.
c.Offset(0, 2) = Replace(c.Offset(0, 1).Value, _
"this title", c.Value, , , vbTextCompare)
Next
End Sub




"JLGWhiz" wrote in message
...

You can give this a try. Expand the range for column A as needed.

Sub stitute()
Dim c As Range, myText As String
For Each c In Range("A2:A4")
myText = c.Offset(0, 1).Value
myText = Replace(myText, "this title", _
c.Value, , , vbTextCompare)
c.Offset(0, 2) = myText
Next
End sub



"andrei" wrote in message
...

Here is what i need from a macro : i have an excel sheet . In A column i
have book titles . In B column i have their descriptions . From time to
time the description contains the keyword "This title" . I need the
macro to find the cells in B column where this keyword is and to
replace it with the text from cell corresponding from A column and put
the new description in C column . Here i have an example :

A1: "Once upon a time" B1: This title is the best book ....
A2: "The killer" B2: John Big wrote this title when he
was....


The result should be :

C1 : "Once upon a time" is the best book...
C2 : John Big wrote "The Killer" when he was...


Can this be done ? Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz

Macro that searches for keyword and replaces it with ...
 

Assuming you **only** want to show results in Column C when Column B has
"this title" in its description, then this macro should do so about as
quickly as is possible...

Sub GetNewDescriptionsOnly()
Dim R As Range, FirstAddress As String
Set R = Columns("B").Find("this title", LookAt:=xlPart, MatchCase:=False)
If Not R Is Nothing Then
FirstAddress = R.Address
Do
R.Offset(0, 1).Value = Replace(R.Value, "this title", _
R.Offset(0, -1).Value, , , vbTextCompare)
Set R = Columns("B").Find("this title", R, _
LookAt:=xlPart, MatchCase:=False)
Loop While Not R Is Nothing And R.Address < FirstAddress
End If
End Sub

--
Rick (MVP - Excel)



"andrei" wrote in message
...

Here is what i need from a macro : i have an excel sheet . In A column i
have book titles . In B column i have their descriptions . From time to
time the description contains the keyword "This title" . I need the
macro to find the cells in B column where this keyword is and to
replace it with the text from cell corresponding from A column and put
the new description in C column . Here i have an example :

A1: "Once upon a time" B1: This title is the best book ....
A2: "The killer" B2: John Big wrote this title when he
was....


The result should be :

C1 : "Once upon a time" is the best book...
C2 : John Big wrote "The Killer" when he was...


Can this be done ? Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz

Macro that searches for keyword and replaces it with ...
 

thank you guys !


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199550

http://www.thecodecage.com/forumz