Macro to use vlookup or match/offset to fill out a table

Hi,

I’m trying to write some code which that will essentially act as if I had a vlookup in every cell in a range. The lookup value is different though for each row. It may be better to use a Match/Offset combo but I’m really not sure.

There are two tables. The first table (Table 1) has different job titles in Column A. Columns B-D contains different Roles in Row 1 (“Role 1”, “Role 2”, “Role 3” etc.). The body of the table is filled with ‘X’s in various cells which indicate which roles are part of each job.

For example:

Table1

The second table is much larger and adds a column ‘Name’ before the Job column. Multiple people can have the same job.

Table2

The macro I’d like to write populates the empty cells in Table 2 with the correct ‘X’s based on the roles that are attached to each Job in Table1. It would start in Column C, Row2 on Table 2, and look for Column 2 Row 2 (“Job1” in this example) in Table 1, then paste whatever value is in the cell with the same Job/Row cell. The macro would then move onto the next column in the row. When it has copied all the cells in that row, it moves to the next row.
The end result would look like this:

Table2

I’ve seen a lot of similar posts but cant quite zero in on what exactly I need for this to work. Any help is much appreciated!
If it matters I’m using Excel 2007 and am a pretty novice VBA coder. I’m good at adapting code, but can’t really write it very well.

Thanks!
Tim

Re: Macro to use vlookup or match/offset to fill out a table

Hi Ger,

Thanks so much for your help! There are a couple reasons I want to do this with a macro. The first is that I don't want there to be formulas in each of the cells. I'm creating a list that needs to be validated and changed by many managers but wanted to provide them a starting place or pre-mapping if you will.

The other reason is that my table will be ~2,000 rows down and 70 columns. I think that many vlookups will crash Excel.

Re: Macro to use vlookup or match/offset to fill out a table

Hey Herbds7 - thats neat! But for some reason when I try to change fill in my real data it says "itilization of the data source failed."

My intended use for this is to embed it in an already existing table - is the concensus that its either too difficult or really not a good idea to write a macro to do this?

Re: Macro to use vlookup or match/offset to fill out a table

Ok awesome - Thanks for that, and makes sense. I'll use the formula format that you suggested. I'll then copy everything and paste values into a new sheet. This 'pre-mapping' will then get distributed to 20 or so users to add additional 'X's in the boxes which is why I didn't want formulas in there.

Now I just have to figure out a good way to merge the 20 or so iterations of this that I'll get back after I distrubute it

Again thanks for the help! I am still curious about how I'd write a macro to do a looping VLookup which would go across multiple columns and then down to the next row. I found some information in Example 3 here (http://www.exceltrick.com/form…okup-in-vba/#comment-1096) but its just for one column.