Macro that searches for keyword and replaces it with ...
in [Programming]
|
From: andrei on 29 Apr 2010 14:21 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/showthread.php?t=199550 http://www.thecodecage.com/forumz
From: JLGWhiz on 29 Apr 2010 15:38 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" <andrei.4a70s4(a)thecodecage.com> wrote in message news:andrei.4a70s4(a)thecodecage.com... > > 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/showthread.php?t=199550 > > http://www.thecodecage.com/forumz >
From: helene and gabor on 29 Apr 2010 15:45 Hello Andrei, enter into C1 =Substitute(B1,"this title",A1,1) (Pull formula down in column C) Best Regards, Gabor Sebo "andrei" <andrei.4a70s4(a)thecodecage.com> wrote in message news:andrei.4a70s4(a)thecodecage.com... > > 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/showthread.php?t=199550 > > http://www.thecodecage.com/forumz > >
From: JLGWhiz on 29 Apr 2010 18:24 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" <JLGWhiz(a)cfl.rr.com> wrote in message news:usXmEO95KHA.4344(a)TK2MSFTNGP04.phx.gbl... > > 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" <andrei.4a70s4(a)thecodecage.com> wrote in message > news:andrei.4a70s4(a)thecodecage.com... >> >> 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/showthread.php?t=199550 >> >> http://www.thecodecage.com/forumz >> > >
From: Rick Rothstein on 29 Apr 2010 23:52
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" <andrei.4a70s4(a)thecodecage.com> wrote in message news:andrei.4a70s4(a)thecodecage.com... > > 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/showthread.php?t=199550 > > http://www.thecodecage.com/forumz > |