Find part of text within cell and replace from another cell

With a simple search and replace I can to this cell formula:

Please Login or Register  to view this content.

Try this in the Worksheet_Change event:

Please Login or Register  to view this content.

I've put this in the attached workbook as well; hope that helps.

I've used this

Please Login or Register  to view this content.

ha - yep; I started down the path i posted and thought "i should have used .Find" but then was lazy & didn't change it

So - sydinstaller ... choice is yours; if you have a LOTS of data, then rwgrietveld's is more efficient and might save a second or so. At least you have 2 ways of doing the same thing

Well Matrixman,

Your code is correct but the filosophy is different. My code is run ONCE and makes the static cell content of Column C into a dynamic Formula.

Your code changes the content of Column C every time I2 is changed.

Both work, but the filosophy is different.

well .. yeah ... i figured you'd just built yours in a module so it can be invoked however the user wanted (button, sheet change, cell edit) ... I just picked on cell edit ... i suppose it depends, but why have the cell as a variable to be changed if it's only going to be done once? in any case - you win the "elegance" vote

I often use this site http://htmlhelp.com/reference/charset/ and you can use the following code to go back and forth.

Asc("'") and chr(39) for Apostrophe

Hi Daniel ... glad it worked for you ... answers to your questions are below:

How did you work out what character number " ' " is? This actually pointed out a typo. I have changed it to 39.

When you're in the code window, CTRL+G will bring up the "immediate" window; you can execute commands here or enquire on values during the run. To work out what character is being used you would enqure on the value of the character using a "?" such as:

Please Login or Register  to view this content.

which will return 146; your correct apostrophe will return 39 as you've discovered. Alternatively, to go the other way, enter:

Please Login or Register  to view this content.

to return the ` character. To actually execute a command, just don't use the "?" ... such as if your code stops before you exit gracefully and you've got events or screenupdating turned off, just as you would in the normal module window:

Please Login or Register  to view this content.

How do I make this a macro that I can choose to run instead of being live? I.E. CTRL + O

I've used what's called an "event module" - which is a reserved module that is triggered when a user does something in the sheet - in this case, it's done when the user edits a cell in the sheet; the code checks to see if it's the cell we're interested in and if so then it runs; if not, it's skipped. Event modules are selectable from the drop-down lists when you're in an object's code window (e.g. Worksheet or Workbook). To run it on demand, you'd just move the code to a non-event module (just one that you've entered directly, either in an object or in a separate module) and allocate a button or some other control to run it (such as a shortcut as you've mentioned). I've modified the attached so that it will now run from the button on the page (not recommended as your layout doesn't allow for reasonable placement), or from CTRL+R (for "replace" - you can't use CTRL+O because that's reserved for "open"), or (still) whenever a user changes that cell. I've created a separate module (outside the sheet object, but it can be inside if you prefer - the difference is that if it's not in a sheet, you have to specify which sheet you are running the code on if it's other than the activesheet). To remove the option to run when the cell is changed, just delete the module Worksheet_Change in its entirety. Same for the button option - it's just there for demonstration ... just remove the button & code. I suspect the one you'll keep is the CTRL+R option ... to set this, just go to Tools / Macros then select Options, and you'll see where I've assigned it.

And lastly, I have virtually no idea about VB. Could you please place comments in the code to say what each part is doing. So is self explanatory but not quite all of it.

Sure - (it's VBA, not VB ) see the attached workbook and below.

Please Login or Register  to view this content.

Hope that helps. MM.