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

[andrei]

Here is what i need from a macro : i have an excel sheet . In A column
have book titles . In B column i have their descriptions . From time t
time the description contains the keyword "This title" . I need th
macro to find the cells in B column where this keyword is and t
replace it with the text from cell corresponding from A column and pu
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 h
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

 

[JLGWhiz]

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

 

[helene and gabor]

Hello Andrei,


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

(Pull formula down in column C)
Best Regards,

Gabor Sebo

 

[JLGWhiz]

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

 

[Rick Rothstein]

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

 

[andrei]

thank you guys !