A Macro to replace a string in a cell with a string from another cell???

[Plica05]

Hi there,

I am trying to write a subroutine Macro that replaces the string "xx" in Column C with the value from the neighbouring Column D.

Column C Column D
-------------------------------- -----------
Overpayment of xx from July 2005 -228.18
Credit Note for xx from Jan 2003 -121.90
Write Off of xx from Oct 2002 -238.24
Overpayment of xx from June 2003 -33.88
Overpayment of xx from Dec 2004 -153.63
Write Off of xx from April 2001 -195.15


For instance, the first entry in Column C above should be:

"Overpayment of -228.18 from July 2005"

This shouldn't be very hard to do I know!

I've tried using the REPLACE() and Find() and search() methods but whenever I run the Macro it says "Subroutine or Function not defined".

I guess I need to start by finding the numerical position of the "xx" substring in the text contents of each cell (from column C) and then use this info with the replace method or something?

I've scoured the web and there's surprisingly little sample code which shows how to replace strings with other strings using VBA!

I'd be very grateful for any help you guys can offer!!

Sean

[Chip Pearson]

Do something like

Range("C1").Value = Replace(Range("C1").Text, "xx",
Range("D1").Value)




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Plica05" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi there,
>
> I am trying to write a subroutine Macro that replaces the
> string "xx"
> in Column C with the value from the neighbouring Column D.
>
> Column C Column D
> -------------------------------- -----------
> Overpayment of xx from July 2005 -228.18
> Credit Note for xx from Jan 2003 -121.90
> Write Off of xx from Oct 2002 -238.24
> Overpayment of xx from June 2003 -33.88
> Overpayment of xx from Dec 2004 -153.63
> Write Off of xx from April 2001 -195.15
>
>
> For instance, the first entry in Column C above should be:
>
> "Overpayment of -228.18 from July 2005"
>
> This shouldn't be very hard to do I know!
>
> I've tried using the REPLACE() and Find() and search() methods
> but
> whenever I run the Macro it says "Subroutine or Function not
> defined".
>
> I guess I need to start by finding the numerical position of
> the "xx"
> substring in the text contents of each cell (from column C) and
> then
> use this info with the replace method or something?
>
> I've scoured the web and there's surprisingly little sample
> code which
> shows how to replace strings with other strings using VBA!
>
> I'd be very grateful for any help you guys can offer!!
>
> Sean
>
>
> --
> Plica05
> ------------------------------------------------------------------------
> Plica05's Profile:
> http://www.excelforum.com/member.php...o&userid=26310
> View this thread:
> http://www.excelforum.com/showthread...hreadid=395842
>

[Plica05]

Hi there

Just a note to say Cheers for the help Chip. Your code worked perfectly first time.. this will save me ALOT of typing!

My regards to all at Pearson Software Consulting!

Sean