Macro word replacement and transfer

**citizensnips09** · 06-29-2012, 03:30 PM

Hey guys not only am I new to this forum but new to the world of Macros in General.

I'm trying to make a set of letters represent a numerical value (ex:xoxoxo = 0.547) but I want to put the numerical value generated in the cell to the immediate right.

I Wrote the code

Cells.replace What:="xoxoxo", Replacement:="0.547", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

using bits and pieces from around the internet and it worked to replace the letters with the numerical value but I need the numerical value to go next to the letters in the cell to the immediate right, not replace it as I had written.

Any help of any kind would be much appreciated.

And again I must stress how new I am to this,

**oeldere** · 06-29-2012, 03:36 PM

With an excel function

CTRL + H

find xoxoxo
replace 0.547

replace all.

**protonLeah** · 06-29-2012, 03:39 PM

Cells.replace What:="xoxoxo", Replacement:="xoxoxo - " & "0.547" ?

**citizensnips09** · 06-29-2012, 03:55 PM

hey guys thanks a lot for the quick responses. ProtonLeah, your code worked really well in placing the numerical value to the right but I was wondering if I could somehow put it in the next cell entirely. I have about 128 variations of xoxoxo to replace with different numerical values so getting this code down once and for all will help me with my research in the future by speeding up the computation of the xoxoxo results. I guess I should have mentioned that earlier.

Thanks again.

**protonLeah** · 06-29-2012, 04:26 PM

You will need loop for that since because you need to know the address of the cell where the xo... is found and use the OFFSET function to move over one column. If all these variations are in the same column, you might be able to use worksheet formulas with a look up table instead of a find and replace macro. A workbook would help.

**citizensnips09** · 06-29-2012, 04:57 PM

Sorry I'm pretty new to this so I'm not exactly sure what you mean by Loop or OFFSET function. I guess this might be an easier question to answer without making me feel so stupid, could you tell me the code that would replace the values of xox.... with the numerical codes in only one column? this way I can just copy the xox.... column to another column before running the macro on just one column. then i wouldn't have to use OFFSET to move them over.

this is what I have.

Cells.replace What:="xoxoxo", Replacement:="0.547", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

now I just want a code added so it only replaces the xo....in one Column (Say Column D)