excel vba enhanced find and replace in cell

Excel vba: 2010
Selecting a range of cells and replacing strings if it finds a match.
not case sensitive

I have some code that will look up values from an Access table.
the 2 fields are txtFind   txtReplace

i',m trying to take the value from  txtFind and see if it exists in the cell replacing it with the value from txtFind

example data in Access   Table:
txtFind          TxtReplace
TOYOTA       TOYOTA



on spreadsheet:

Toyota MFG CAR PLANT

WHAT I NEED TO HAPPEN:

TAKE THIS  "Toyota MFG CAR PLANT"
AND MAKE THIS
"TOYOTA"

ALL THE CODE IS DOING NOW IS REPLACING THE WORD "TOYOTA" with "TOYOTA" and the output is still
"Toyota MFG CAR PLANT"


Sub ReplaceColText()
Dim txtFind As String
Dim txtReplace As String
Dim Source As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim k As String
Dim j As String

 
    db.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Program Files\Enterprise\Enterprise\Replace.accdb"

' RESET SELECTION
Cells(2, 2).Select

 'RESET RANGE
 Macro4


Source = "SELECT txtfind, txtreplace from tblFindReplaceMfg"

rs.Open Source, db, adOpenKeyset, adLockOptimistic
   rs.MoveFirst
  On Error Resume Next
   Do While Not rs.EOF
       txtFind = rs("txtFind")
       txtReplace = rs("txtReplace")
       Selection.Replace What:=txtFind, Replacement:=txtReplace, lookat:=xlPart, _
       SearchOrder:=xlByColumns, MatchCase:=False
      rs.MoveNext
   Loop

' RESET SELECTION
Cells(2, 2).Select
'MsgBox "Mfgnames Are Cleaned.", vbInformation, "Customer Mfgnam Cleaning"
          
end sub

Open in new window



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
You should replace lines 30 and 31 with something like

if instr(selection,txtfind)>0 then selection=txtreplace
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
perfect...Thanks!
0
FordraidersAuthor Commented:
SYED, ..

I have discovered a problem. This will replace every cell in the selection...
I need it to loop through each cell in the selection, not change every cell in the selection?

fordraiders
0
Saqib Husain, SyedEngineerCommented:
Try

for each cel in selection
if instr(cel,txtfind)>0 then cel=txtreplace
next cel
0
FordraidersAuthor Commented:
thanks worked like a charm now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.