Find in Column A and Remove from Column B

[Guest]

Hey Guys, I was wondering if there was a way to create a macro (not
substitute or concatonate) that would Find information from Column A and
Remove it from Column B.
For Example:

from
| Column A | Column B|
| American Online | American Online, Bob |
| Google.com | Google.com 123 Fake Street |

to
| Column A | Column B|
| American Online | , Bob |
| Google.com | 123 Fake Street |

THANKS!!!!

~johnny b

 

[Guest]

try this code

Sub CompareAB()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set MyRange = Range(Cells(1, 1), Cells(Lastrow, 1))

For Each cell In MyRange

If StrComp(cell, cell.Offset(rowoffset:=0, columnoffset:=1)) = 0 Then

cell.Delete Shift:=xlToLeft
End If


Next cell

End Sub

 

[Guest]

hey joel thanks again, i think you mean for me to change the formula to

Sub CompareAB()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set MyRange = Range(Cells(1, 1), Cells(Lastrow, 1))

For Each cell In MyRange

If StrComp(cell, cell.Offset(rowoffset:=0, columnoffset:=1)) = 0 Then

cell.Delete Shift:=xlToLeft

If InStr(cell.Offset(rowoffset:=0, columnoffset:=1), cell) > 0 Then

cell.Delete Shift:=xlToLeft

End If


Next cell

End Sub

it says "Compile Error > Next without For" ?????

can you help me out?

thanks!

~johnny b

 

[Guest]

Yea, the word to somehow disappearred from my response.