Excel macro for 'find and replace' values loop

[StandardDeviation]

Hi,

I have an Excel doc containing a table with 4 columns - Contact Name, Contact Company, Contact Job Title, Owner. The Owner column (col. D), displays the names of any and all company personnel (past and present) who know that particular contact. If there are multiple Owners, each name is separated by a semi-colon - see Table 1 below.

Tab 2 contains a list of names consisting of former company personnel - see Table 2 below.

I need a code to remove the names of former company personnel found within the Owner column. Starting with D2, the macro would compare the names in this cell with all of the names in Tab 2 and remove any matching ones. In effect, the "replace" value is a blank. Then it needs to perform the same operation for cells D3, D4, and so on - see Result table below.


Table 1 (on tab 1)
[TABLE="width: 625"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Company
[/TD]
[TD]Job Title
[/TD]
[TD]Owner
[/TD]
[/TR]
[TR]
[TD]John Green
[/TD]
[TD]ABC Inc
[/TD]
[TD]Accounting
[/TD]
[TD]Lincoln, Abraham; Day, Doris; Brock, Isaac; Cruz, Penelope
[/TD]
[/TR]
[TR]
[TD]Frieda Kahlo
[/TD]
[TD]XYZ Inc
[/TD]
[TD]HR
[/TD]
[TD]Craig, Daniel; Houdini, Harry; Cruz, Penelope
[/TD]
[/TR]
[TR]
[TD]Geddy Lee
[/TD]
[TD]PPP Inc
[/TD]
[TD]Operations
[/TD]
[TD]Wyle, Noah; Lincoln, Abraham; Martin, Dean
[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 (on tab 2)
[TABLE="width: 625"]
<tbody>[TR]
[TD]Former Company Personnel
[/TD]
[/TR]
[TR]
[TD]Lincoln, Abraham
[/TD]
[/TR]
[TR]
[TD]Martin, Chris
[/TD]
[/TR]
[TR]
[TD]Cruz, Penelope
[/TD]
[/TR]
[TR]
[TD]Armstrong, Louis
[/TD]
[/TR]
</tbody>[/TABLE]


Result Table
[TABLE="width: 625"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Company
[/TD]
[TD]Job Title
[/TD]
[TD]User
[/TD]
[/TR]
[TR]
[TD]John Green
[/TD]
[TD]ABC Inc
[/TD]
[TD]Accounting
[/TD]
[TD]Day, Doris; Brock, Isaac;
[/TD]
[/TR]
[TR]
[TD]Frieda Kahlo
[/TD]
[TD]XYZ Inc
[/TD]
[TD]HR
[/TD]
[TD]Craig, Daniel; Houdini, Harry;
[/TD]
[/TR]
[TR]
[TD]Geddy Lee
[/TD]
[TD]PPP Inc
[/TD]
[TD]Operations
[/TD]
[TD]Wyle, Noah; Martin, Dean
[/TD]
[/TR]
</tbody>[/TABLE]


Your help is much appreciated! Thanks.

 

[dfsobral]

try this

i'm assuming all information is on the same sheet

Sub ReplaceOwners()


Dim rngTable2 As Range
Dim rngOwner As Range
Dim cell As Range


Set rngTable2 = Range("F2:F5")  'adjust the range to your case
Set rngOwner = Range("D2:D5")   'adjust the range to your case


For Each cell In rngTable2
    rngOwner.Replace cell.Value & "; ", ""
Next cell


End Sub