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

 

[GTO]

Please use the [code]'your code[/code] tags and post and post the code you have thus far. Leastwise to me, it is unclear whether you are using the WS function FIND or vba .Find.

 

[VoG]

Try including the parameter

lookat:=xlwhole

 

[jkelbel]

For i = 1 To 1450
Cells.Replace What:=Cells(i, 9), Replacement:=Cells(i, 10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next i

 

[VoG]

Change xlpart to xlwhole

 

[GTO]

Of course Peter is right on course Change:

For i = 1 To 1450
Cells.Replace What:=Cells(i, 9), Replacement:=Cells(i, 10), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next i

<!-- / message -->

 

[jkelbel]

More explanation is needed

Tried the Whole parameter

If the value - say r4c23 is in a cell all by its self it is replaced bt if the

value is in a cell as text like r4c23/r5c24 * 4 the value is not found and replaced

I have tried to add a space like this r4c23 / r5c24 * 4 and it is not replaced.

 

[VoG]

Perhaps

For i = 1 To 1450
Cells.Replace What:=Cells(i, 9), Replacement:=Cells(i, 10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, LookIn:=xlFormulas

Next i

 

[jkelbel]

<TABLE style="WIDTH: 359pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=479 border=0><COLGROUP><COL style="WIDTH: 359pt; mso-width-source: userset; mso-width-alt: 17517" width=479><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 359pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=479 height=17> ( ( ( ( r36c3 * 10 * 0.95 * 1.34 * r245c19 * 0.04 * 100 / r30c21 ) ) ) )

The above is in a cell as text

I would like to find and replace r36c3 with HGB
I am using the Whole parameter. r36c3 is not found

I have r36c3 in my find column and HGB in my replace column












</TD></TR></TBODY></TABLE>

 

[GTO]

<TABLE style="WIDTH: 359pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=479 border=0><COLGROUP><COL style="WIDTH: 359pt; mso-width-source: userset; mso-width-alt: 17517" width=479><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 359pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=479 height=17>( ( ( ( r36c3 * 10 * 0.95 * 1.34 * r245c19 * 0.04 * 100 / r30c21 ) ) ) )
( ( ( ( r36c37 * 10 * 0.95 * 1.34 * r245c19 * 0.04 * 100 / r30c21 ) ) ) )


I would like to find and replace r36c3 with HGB
I have r36c3 in my find column and HGB in my replace column

</TD></TR></TBODY></TABLE>
Okay, please note the difference in that I added the 7.

Now going by one of your first posts/raised issues, would this be a fair statement(?):

If r36c3 is in my find column, then I would like it to find a partial match such as either r36c3 or r36c37.