find and replace exact only

[jkelbel]

I have a need to find values in column A with values in column B.
I have a simple loop using cell(i,1) and cell(i,2).

That works fine.
The problem is I am finding something like "r23c21" and replacing that with "text". I am finding r23c217 and the replacement yields "text7". I need a find 'exact only' type of parameter or something. I have about 1500 replacements to automate. I have added a space after the find values, but FIND seems to strip that out.

thanks in advance

 

[jkelbel]

No, the fair statement is that I only wish to find r36c3
exactly and only --I do not wish to find r36c37

 

[jkelbel]

No the fair statement is I wish to only and exactly find r36c3
I do not wish to find r36c37.

the cells contain only text - jst to be clear

 

[GTO]

Okay, and thank you for the clarification. I do not think you will be able to use .Find/.Replace effectively for this. As you are seeing, we're either replacing an entire string (the contents of a cell in this case), or if we replace a partial, .Replace has no ability to decipher word boundaries and such.

We may need help on an effective pattern, but see if a simple one works. For this:

Excel Workbook
	A	B	C
2	r36c3	HGB	( ( ( ( r36c3 * 10 * 0.95 * 1.34 * r245c19 * 0.04 * 100 / r30c21 ) ) ) )
3	r30c21	r30c32	((((r36c3*10*0.95*1.34*r245c19*0.04*100/r30c21))))
4			( ( ( ( r36c37 * 10 * 0.95 * 1.34 * r245c19 * 0.04 * 100 / r30c21 ) ) ) )
5			((((r36c37*10*0.95*1.34*r245c19*0.04*100/r30c21))))
Sheet3

Try, In a Standard Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa()<br>Dim _<br>rngLookFor          <SPAN style="color:#00007F">As</SPAN> Range, _<br>rngDataCell         <SPAN style="color:#00007F">As</SPAN> Range, _<br>strFormula          <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngLookFor <SPAN style="color:#00007F">In</SPAN> Range("A2:A3")<br>        For <SPAN style="color:#00007F">Each</SPAN> rngDataCell <SPAN style="color:#00007F">In</SPAN> Range("C2:C5")<br>            <br>            strFormula = rngDataCell.Text<br>            <SPAN style="color:#00007F">If</SPAN> ReturnReplacement(rngLookFor.Text, rngLookFor.Offset(, 1).Text, strFormula) <SPAN style="color:#00007F">Then</SPAN><br>                rngDataCell.Value = strFormula<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> ReturnReplacement(LookFor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>                           ReplaceWith <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>                           FormulaString <SPAN style="color:#00007F">As</SPAN> String _<br>                           ) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Static</SPAN> REX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN> <SPAN style="color:#007F00">'<--- RegExp</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> REX <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>        REX.Global = <SPAN style="color:#00007F">False</SPAN><br>        REX.IgnoreCase = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Pattern = "\b" & LookFor & "\b"<br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Test(FormulaString) <SPAN style="color:#00007F">Then</SPAN><br>            FormulaString = .Replace(FormulaString, ReplaceWith)<br>            ReturnReplacement = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Hope that helps,

Mark

 

[jkelbel]

Thanks to everyone who contributed. I needed to know the limits of find/replace and regular expression is the way to go. So thanks a ton.