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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.