How do i replace part of content in a cell

maryam62

Honorable
May 5, 2012
1
0
10,510
hi. i have line of words in every cell. i want replace for example one of the words in a cell with another word in other sheet.
example:
Cells(1, 1)=book wall TV
Sheet2!Cells(1, 1)=wall
sheet2!cells(1,2)="1"
result:Cells(1, 1)=book 1 TV


now what is wrong with this code?

Sub Button1_Click()
Columns("b") = ""
On Error Resume Next
Dim c As Range, d As Range
For Each c In Sheet1.Range("a1:a2")
If c.Value <> "" Then
For Each d In Sheet2.Range("a1:a7")
If d.Value <> "" And InStr(c.Value, d.Value) <> 0 Then
Selection.Replace What:=c.Offset(0, 1).Value ,Replacement:=d.Offset(0, 1).Value, LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=False
End If
Next d
End If
Next c
End Sub


dic.jpg