troubleshooting Question

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

Avatar of aditee
aditee asked on
VBAMicrosoft ExcelMicrosoft Office
2 Comments1 Solution422 ViewsLast Modified:
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
NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros