Find and replace multiple values from a list

[ryanpetersen]

Hi,


I was wondering if someone could please help me with the following.


I have 'Table' worksheet with sales figures and I'm trying to work out how to make a macro go through a list on the 'Lookup' worksheet, find and replace the values in the 'Table' worksheet.
For example, to search "Product 2" in column A of 'Table' worksheet and replace with "DELETED Product 2" (data from the 'Lookup' worksheet). This would continue until all values have been checked and replaced. Also on the 'Table' worksheet, a product might appear multiple times. For example Product 2 might need to be replaced 3 or 4 times before it starts replacing "Product 4" with "DELETED Product 4"
The end result I'm trying to get to is similar to 'Outcome' below.


Many thanks for your help.

Table
Product 1 $100,000
Product 2 $5,000
Product 3 $120,000
Product 4 $2,000

Lookup
Product 2 = DELETED Product 2
Product 4 = DELETED Product 4

Outcome
Product 1 $100,000
DELETED Product 2 $5,000
Product 3 $120,000
DELETED Product 4 $2,000

 

[WarPigl3t]

Sub Macro1()
    lastLine = Sheets("Table").Range("A" & Rows.Count).End(xlUp).Row
    For Each Cell In Sheets("Outcome").Range("A1:A" & lastLine).Cells
        i = i + 1
        Sheets("Outcome").Range("A" & i).Value = "=IF(IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & ")=0,"""",IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & "))"
        Sheets("Outcome").Range("A" & i).Value = Sheets("Outcome").Range("A" & i).Value
        Sheets("Outcome").Range("B" & i).Value = "=Table!B" & i
        Sheets("Outcome").Range("B" & i).Value = Sheets("Outcome").Range("B" & i).Value
    Next Cell
End Sub

 

[WarPigl3t]

Actually the code works but doesn't make sense. Use this instead.

Sub Macro1()     lastLine = Sheets("Table").Range("A" & Rows.Count).End(xlUp).Row
    i = 0     Do Until i = lastLine + 1         i = i + 1         Sheets("Outcome").Range("A" & i).Value = "=IF(IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & ")=0,"""",IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & "))"         Sheets("Outcome").Range("A" & i).Value = Sheets("Outcome").Range("A" & i).Value         Sheets("Outcome").Range("B" & i).Value = "=Table!B" & i         Sheets("Outcome").Range("B" & i).Value = Sheets("Outcome").Range("B" & i).Value     Loop End Sub</pre>

 

[snb_]

Sub M_snb()
   sn=sheets("lookup").cells(1).currentregion

   for j=1 to ubound(sn)
      sheets("table").columns(1).replace sn(j,1),"deleted"
   next
End Sub

 

[WarPigl3t]

Actually the code works but doesn't make sense. Use this instead.

Sub Macro1()     lastLine = Sheets("Table").Range("A" & Rows.Count).End(xlUp).Row
    i = 0     Do Until i = lastLine + 1         i = i + 1         Sheets("Outcome").Range("A" & i).Value = "=IF(IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & ")=0,"""",IFERROR(VLOOKUP(Table!A" & i & ",Lookup!A:B,2,FALSE),Table!A" & i & "))"         Sheets("Outcome").Range("A" & i).Value = Sheets("Outcome").Range("A" & i).Value         Sheets("Outcome").Range("B" & i).Value = "=Table!B" & i         Sheets("Outcome").Range("B" & i).Value = Sheets("Outcome").Range("B" & i).Value     Loop End Sub

It got all messed up when I copied it. Here it the right one.
Actually the code works but doesn't make sense. Use this instead.

Sub Macro1()     
     lastLine = Sheets("Table").Range("A" & Rows.Count).End(xlUp).Row
     i = 0     
     Do Until i = lastLine + 1         
          i = i + 1         
           Sheets("Outcome").Range("A" & i).Value =  "=IF(IFERROR(VLOOKUP(Table!A" & i &  ",Lookup!A:B,2,FALSE),Table!A" & i &  ")=0,"""",IFERROR(VLOOKUP(Table!A" & i &  ",Lookup!A:B,2,FALSE),Table!A" & i & "))"         
          Sheets("Outcome").Range("A" & i).Value = Sheets("Outcome").Range("A" & i).Value     
          Sheets("Outcome").Range("B" & i).Value = "=Table!B" & i         
          Sheets("Outcome").Range("B" & i).Value = Sheets("Outcome").Range("B" & i).Value     
     Loop 
End Sub