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

IrishMist1748

Board Regular
Joined
Sep 27, 2010
Messages
131
Hello!

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


Code:
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>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

syntaxed

Board Regular
Joined
Feb 22, 2011
Messages
119
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:

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

And if you want to preserve the space before your RepStr, then try:
Rich (BB code):
Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" " & RepStr
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,042
Messages
5,526,420
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top