Improper "syntax" returns var name/value in find-replace macro!

[IrishMist1748]

Hello!

I am using the following macro to auto find and replace multiple items at once:

Sub FindReplace()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 145
    FindStr = Sheet2.Range("A" & i).Value
    RepStr = Sheet2.Range("B" & i).Value
    
    Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" RepStr"
Next i
End Sub

Sheet2 contains my list of 'finds' in column A and 'replaces' in column B. Sheet 1, column B is the target for the find and replacement.

Everything works fine but it is OBVIOUS that I have a problem with my syntax somewhere because I get some funny output on my replacement. It seems that the "word" 'Rep' and 'RepStr' and being part of the replacement!

Example:
Find: <table x:str="" style="border-collapse: collapse; width: 368pt;" width="490" border="0" cellpadding="0" cellspacing="0"><col style="width: 368pt;" width="490"><tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 368pt;" width="490" height="17">14KWHT W/YEL TRIM RD DIA GENT RING D.25TW</td> </tr></tbody></table>Replacement: <table x:str="" style="border-collapse: collapse; width: 368pt;" width="490" border="0" cellpadding="0" cellspacing="0"><col style="width: 368pt;" width="490"><tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 368pt;" width="490" height="17">14 Rep Rep RepStrtr RepStrHT RepStr/ RepStr TRIM RD Rep Rep RepStrtr Rep Rep RepStrtr RING D.25T RepStr

Any ideas on what I am doing wrong here?
</td> </tr></tbody></table>

 

[syntaxed]

You've got the RepStr variable in quotes. VBA's treating it as the string " RepStr" instead of the value the RepStr variable is representing. Try:

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=RepStr

And if you want to preserve the space before your RepStr, then try:

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" " & RepStr

 

[IrishMist1748]

Thank you!

That did the trick!

 

[njimack]

Remove the quote marks around RepStr