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)

Name	Company	Job Title	Owner
John Green	ABC Inc	Accounting	Lincoln, Abraham; Day, Doris; Brock, Isaac; Cruz, Penelope
Frieda Kahlo	XYZ Inc	HR	Craig, Daniel; Houdini, Harry; Cruz, Penelope
Geddy Lee	PPP Inc	Operations	Wyle, Noah; Lincoln, Abraham; Martin, Dean

<tbody>
</tbody>

Table 2 (on tab 2)

Former Company Personnel

Lincoln, Abraham

Martin, Chris

Cruz, Penelope

Armstrong, Louis

<tbody>
</tbody>

Result Table

Name	Company	Job Title	User
John Green	ABC Inc	Accounting	Day, Doris; Brock, Isaac;
Frieda Kahlo	XYZ Inc	HR	Craig, Daniel; Houdini, Harry;
Geddy Lee	PPP Inc	Operations	Wyle, Noah; Martin, Dean

<tbody>
</tbody>

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