# Find and replace multiple values from a list

#### ryanpetersen

##### New Member
Hi,

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.

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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I could figure out how to do this in vba, but I don't want to because of how you formatted the lookup.
Lookup
Product 2 = DELETED Product 2
Product 4 = DELETED Product 4
Does it really look like that? Like does A1 actually say "Product 2 = DELETED Product 2"? It says all of that? Why not just "DELETED Product 2"? If you did it that way, then I could run the REPLACE function to remove DELETED from the string value. Then I could run the TRIM function to get rid of spaces on the ends. Then I could do a wildcard search of "*Product 2*" for every non empty row. If your lookup table really looks like how you described with an equal sign and everything as part of the string, you made it too complicated and I don't want to work around that. I don't think anyone else wants to either or you would have had a reply by now. Can you change the lookup table to something easier? You don't even need to include the word deleted in the value. If the purpose of the lookup table is to log deleted items, then obviously everything in that table is deleted, so why state it in the cell value? All that does is create complication when writing vb code. Simple your dataset up and I could accomplish this in under 30 microts.

Thanks for your reply. I couldn't attach a file so I tried copying the information. Sorry it was unclear.
The data sits in different columns. I've added a comma below for each new column.

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

 A 1 product 1 2 product 2

<tbody>
</tbody>
In future posts, click on Go Advanced next to the Post Quick Reply button and insert a table.

Thanks for the tip. Please let me know if you'd like the information re-posted in tables to make it easier.

Alright lets take a look at the Outcome sheet. In cell A1 I want you to enter this formula...
Code:
``=IFERROR(VLOOKUP(Table!A1,Lookup!A:B,2,FALSE),Table!A1)``

In cell B1 enter this formula...
Code:
``=Table!B1``

Copy the code down the column. Done. I know you said you wanted VBA, but this was way to easy as a formula.

Last edited:
Thanks for your quick solution WarPiglet.
I'd like to use a macro to apply the change because there are several data sheets (table). Are you able to help with the VBA code please?

Yes I can do it. I'm going to assume you want a subroutine rather than a function? Is this going to be an on change event or at the push of a button?

I'd like a push of the button to run it please
A standard subroutine would be great.

Taking a bit longer than I hoped. Have to start over. I accidentally created an infinite loop that just crashed my system.

Replies
5
Views
181
Replies
3
Views
187
Replies
4
Views
251
Replies
3
Views
211
Replies
9
Views
451

1,221,522
Messages
6,160,313
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back