Find in Column A and Remove from Column B
 

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

Find in Column A and Remove from Column B
 

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


"Johnny B" wrote:

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

Find in Column A and Remove from Column B
 

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

"Joel" wrote:

then use instr

from:
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


"Johnny B" wrote:

Thanks Joel!, this only works if Column A is identical to the entire cell in
column B.
For example

|American Online | American Online |

running the macro will give

|American Online | (empty) |

however, if there is

|American Online | American Online, John Johnson |

nothing happens. Running the macro will give:

|American Online | American Online, John Johnson |

thanks for your help!

~johnny b

"Joel" wrote:

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


"Johnny B" wrote:

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

Find in Column A and Remove from Column B
 

Yea, the word to somehow disappearred from my response.

"Johnny B" wrote:

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

"Joel" wrote:

then use instr

from:
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


"Johnny B" wrote:

Thanks Joel!, this only works if Column A is identical to the entire cell in
column B.
For example

|American Online | American Online |

running the macro will give

|American Online | (empty) |

however, if there is

|American Online | American Online, John Johnson |

nothing happens. Running the macro will give:

|American Online | American Online, John Johnson |

thanks for your help!

~johnny b

"Joel" wrote:

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


"Johnny B" wrote:

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