Macro to find value from one cell and replace to another

**onsvsub** · 11-21-2012, 03:56 PM

My first post, apologies if this has been answered elsewhere, if it has, I couldn't find it!

Example1.xls

In the attached example I need a macro that will look through the data from A2 to I13. Wherever it sees a number it needs to replace it with one of the names in B19 to B30. For example, where number 1 is found it is automatically replaced with the value in B19 (adam). If the values from B19 to 30 change then they need to change in the table. IE I get a list of 12 names per season and it will work out the batting order for 9 weeks.

I've tried recording a find and replace, played around with substitute but no joy. I'm wanting something that will find a value in a cell and replace with a value from another cell, then it also needs to loop. I'm really new to writing macros so any help would be greatly appreciated. Thanks very much.

Scott

**JBeaucaire** · 11-21-2012, 04:22 PM

No VBA needed. Just make a duplicate of your table off to the right. Copy the whole table and paste in K1. Now use a "lookup" formula for the names that go with the positional values from the first table.

The first formula in K2 is: =VLOOKUP(A2, $A$19:$B$30, 2, 0)

That is copied down and across the new table to display the names.