Solved replace part of content of a cell in excel

May 4, 2012 at 20:22:25
Specs: Windows XP
hi. i have line of words in every cell. i want replace for example one of the words in cells(1,1) with another word there is in sheet2!cells(1,2) if InStr(Cells(1, 1), Sheet2!Cells(1, 1))<> 0.

Cells(1, 1)=book door bag
Sheet2!Cells(1, 1)=door
result:Cells(1, 1)=book 2 bag

now what is wrong with this code?

If Sheet2!Cells(1, 2) <> "" And InStr(Cells(1, 1), Sheet2!Cells(1, 1)) <> 0 Then
Selection.Replace What:=Cells(1, 1), Replacement:=Sheet2!Cells(1, 2),_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
end if

See More: replace part of content of a cell in excel

Report •

May 5, 2012 at 09:18:37
✔ Best Answer
There's a lot wrong with your code, starting with a number of syntax errors.

1 - Sheet2!Cells(1, 1) is not correct.

If Sheet2 is the second sheet in the workbook, counting from left to right, then the correct syntax would be:

Sheets(2).Cells(1, 1)

If you want to refer to the sheet by name, regardless of where it is positioned in the workbook, the correct syntax would be:

Sheets("Sheet2").Cells(1, 1)

2 - You do not have a space before the Line Continuation character (the underscore).

Replacement:=Sheet2!Cells(1, 2),_

should be:

Replacement:=Sheets(2).Cells(1, 2), _

3 - In some parts of your code you refer to specific cells (Cells(1, 1), Sheets(2).Cells(1, 1), etc) yet you are doing your replacement in the Selected cell.


That will give you the result you asked for in your example only if Cells(1, 1) is the current Selection.

Since you rarely have to actually Select a range in VBA to perform an action on it, it might be better to use:

Cells(1, 1).Replace

4 - So, after you fix all of the syntax errors and actually get the code to run, you are going to find that your end result in Cells(1, 1) will be 2, not book 2 bag because you are using:

Selection.Replace What:=Cells(1, 1), 

which tells VBA to replace the entire contents of the cell.

What you probably want to use is:

Cells(1, 1).Replace What:=Sheets(2).Cells(1, 1), 

By making the changes noted above I was able to get the result you are looking for.

I'll leave it up to you to try and fix your code by yourself. It'll be a great learning experience.

P.S. Please click on the following line and read the instructions found via that link before posting anymore code in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question