Link to home
Start Free TrialLog in
Avatar of aditee
aditee

asked on

If the cell contains the particular text then to replace the adjacent cell text with other text using vba in excel.

Hello,

1. I want to check all cells in a column if it contains certain text , say, "tom nd jerry". And if cell contains the given text then to replace the adjacent cell text with another text, say , "tom".
2. But if the adjacent cell contains the text "others" then it should not replace the text. I need to do  using vba in excel.

I tried using below macro
Dim r3 as range
Set r3 = ActiveSheet.Range("A:A")
For Each cell In r3.Cells

If InStr(1, cell, "Tom nd Jerry") > 0 Then
cell.Offset(0, 1).Value = "Tom"
End If
Next
It works fine. But the later part of the question, that is, if the adjacent cell contains the specific text, say, "others", then I don't want to replace it. I am not able to write macro for it. And again the above code takes lot of time, so how to reduce the execution time?

I tried something like this for later part. I don't know how to write macro for it.

Dim r3 as range
Set r3 = ActiveSheet.Range("A:A")
For Each cell In r3.Cells

If InStr(1, cell, "Tom nd Jerry") > 0 & InStr(1, cell.offset(0,1),"others") <> 0 Then
cell.Offset(0, 1).Value = "Tom"
End If
Next

Please help me out with the above problem. Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aditee
aditee

ASKER

Thank you so much. It worked very well.