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.