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.
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")
cell.Offset(0, 1).Value = "Tom"
End If
Next
Please help me out with the above problem. Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER