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
Job | Role1 | Role2 | Role3 |
Job1 | X | X | |
Job2 | X | X | |
Job3 | X | X |
The second table is much larger and adds a column ‘Name’ before the Job column. Multiple people can have the same job.
Table2
Name | Job | Role1 | Role2 | Role3 |
Tom | Job1 | |||
Dick | Job2 | |||
Harry | Job1 |
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
Name | Job | Role1 | Role2 | Role3 |
Tom | Job1 | X | X | |
Dick | Job2 | X | X | |
Harry | Job1 | X | X |
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