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 bu
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 the
use this info with the replace method or something?

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

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

Sea

--
Plica0
-----------------------------------------------------------------------
Plica05's Profile: http://www.excelforum.com/member.php...fo&userid=2631
View this thread: http://www.excelforum.com/showthread.php?threadid=39584

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"
wrote in message
...

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

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


--
Plica05
------------------------------------------------------------------------
Plica05's Profile: http://www.excelforum.com/member.php...o&userid=26310
View this thread: http://www.excelforum.com/showthread...hreadid=395842