Finding values within text and substituting with alternate values.

Hi All,

I have a table with two columns, column A contains old values and column B
contains new values.

I am trying to create a program, where I paste a piece of text into a
separate worksheet, which contains old values (can be randomly within the
text, in any cell). I want excel the search through the text and find any
old values from column A and replace them with new values, i.e. if value in
A7 is found in text, then replace with its corresponding new value from B7.

Also, I don't want the code to fall over when no values are found, and for
it to move to the next value in the table, i.e. old value in A5 returns no
match in text, so move onto old value in A6 and try to find a match.

Is this possible to achieve? Any help will be much appreciated.


Kind Regards,

Bhupinder

the macro recorder is your friend
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 1/23/2006 by Don Guillett
'

'
Range("C2:C11").Select
Selection.Replace What:="aa", Replacement:="bb", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"Bhupinder Rayat" <[email protected]> wrote in
message news:[email protected]...
> Hi All,
>
> I have a table with two columns, column A contains old values and column
> B
> contains new values.
>
> I am trying to create a program, where I paste a piece of text into a
> separate worksheet, which contains old values (can be randomly within the
> text, in any cell). I want excel the search through the text and find any
> old values from column A and replace them with new values, i.e. if value
> in
> A7 is found in text, then replace with its corresponding new value from
> B7.
>
> Also, I don't want the code to fall over when no values are found, and for
> it to move to the next value in the table, i.e. old value in A5 returns no
> match in text, so move onto old value in A6 and try to find a match.
>
> Is this possible to achieve? Any help will be much appreciated.
>
>
> Kind Regards,
>
> Bhupinder

It is not as straight-forward as that.

I want to find all instances in a block of text contained in sheet 2 ,of the
value in cell A1, sheet 1, and replace it with the value from cell B1 sheet
1.

e.g
A1 B1
xxx yyy
A2 B2
aaa bbb
A3 B3
ccc ddd

text in sheet 2...

field b0 @avg ("xxx" , DATEFIRST, DATELAST) ;
field B0 b0 write xxx
field b2 @avg ("ccc" , DATEFIRST, DATELAST) ;
field B2 b2 write ccc

text after replacements...

field b0 @avg ("yyy" , DATEFIRST, DATELAST) ;
field B0 b0 write yyy
field b2 @avg ("ddd" , DATEFIRST, DATELAST) ;
field B2 b2 write ddd

If you notice, in the original text, there was no text string "aaa", so I
want the code the continue throught the list and search for "ccc" without
falling over as it found no instances of "aaa".


Sorry if wasn't clear before....


Thank you,

Bhupinder.
"Don Guillett" wrote:

> the macro recorder is your friend
> Sub Macro5()
> '
> ' Macro5 Macro
> ' Macro recorded 1/23/2006 by Don Guillett
> '
>
> '
> Range("C2:C11").Select
> Selection.Replace What:="aa", Replacement:="bb", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Bhupinder Rayat" <[email protected]> wrote in
> message news:[email protected]...
> > Hi All,
> >
> > I have a table with two columns, column A contains old values and column
> > B
> > contains new values.
> >
> > I am trying to create a program, where I paste a piece of text into a
> > separate worksheet, which contains old values (can be randomly within the
> > text, in any cell). I want excel the search through the text and find any
> > old values from column A and replace them with new values, i.e. if value
> > in
> > A7 is found in text, then replace with its corresponding new value from
> > B7.
> >
> > Also, I don't want the code to fall over when no values are found, and for
> > it to move to the next value in the table, i.e. old value in A5 returns no
> > match in text, so move onto old value in A6 and try to find a match.
> >
> > Is this possible to achieve? Any help will be much appreciated.
> >
> >
> > Kind Regards,
> >
> > Bhupinder
>
>
>

try this loop

Sub findreplaceloop()
For Each c In Sheets("sheet7").Range("d1:d2")
Sheets("sheet6").Range("f1:f4").Replace What:=c, Replacement:=c.Offset(, 1),
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next c
End Sub
--
Don Guillett
SalesAid Software
[email protected]
"Bhupinder Rayat" <[email protected]> wrote in
message news:[email protected]...
> It is not as straight-forward as that.
>
> I want to find all instances in a block of text contained in sheet 2 ,of
> the
> value in cell A1, sheet 1, and replace it with the value from cell B1
> sheet
> 1.
>
> e.g
> A1 B1
> xxx yyy
> A2 B2
> aaa bbb
> A3 B3
> ccc ddd
>
> text in sheet 2...
>
> field b0 @avg ("xxx" , DATEFIRST, DATELAST) ;
> field B0 b0 write xxx
> field b2 @avg ("ccc" , DATEFIRST, DATELAST) ;
> field B2 b2 write ccc
>
> text after replacements...
>
> field b0 @avg ("yyy" , DATEFIRST, DATELAST) ;
> field B0 b0 write yyy
> field b2 @avg ("ddd" , DATEFIRST, DATELAST) ;
> field B2 b2 write ddd
>
> If you notice, in the original text, there was no text string "aaa", so I
> want the code the continue throught the list and search for "ccc" without
> falling over as it found no instances of "aaa".
>
>
> Sorry if wasn't clear before....
>
>
> Thank you,
>
> Bhupinder.
> "Don Guillett" wrote:
>
>> the macro recorder is your friend
>> Sub Macro5()
>> '
>> ' Macro5 Macro
>> ' Macro recorded 1/23/2006 by Don Guillett
>> '
>>
>> '
>> Range("C2:C11").Select
>> Selection.Replace What:="aa", Replacement:="bb", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>> ReplaceFormat:=False
>> End Sub
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Bhupinder Rayat" <[email protected]> wrote in
>> message news:[email protected]...
>> > Hi All,
>> >
>> > I have a table with two columns, column A contains old values and
>> > column
>> > B
>> > contains new values.
>> >
>> > I am trying to create a program, where I paste a piece of text into a
>> > separate worksheet, which contains old values (can be randomly within
>> > the
>> > text, in any cell). I want excel the search through the text and find
>> > any
>> > old values from column A and replace them with new values, i.e. if
>> > value
>> > in
>> > A7 is found in text, then replace with its corresponding new value from
>> > B7.
>> >
>> > Also, I don't want the code to fall over when no values are found, and
>> > for
>> > it to move to the next value in the table, i.e. old value in A5 returns
>> > no
>> > match in text, so move onto old value in A6 and try to find a match.
>> >
>> > Is this possible to achieve? Any help will be much appreciated.
>> >
>> >
>> > Kind Regards,
>> >
>> > Bhupinder
>>
>>
>>