VLOOKUP to Find and Replace for entire workbook

I have a very large spreadsheet that I am using to track/analyze enterprise roles and the permissions that go along with each role. On the first sheet, I have a list of all employees (Name, Title, Department, etc) and on another sheet, I have a list of all Security Groups and Distribution Lists (with Members.) What I need to do is create a vba script that completes (1) a VLookUp using the Name column of the Employee sheet as the Criteria and then check against the first column in the Groups/Lists sheet for the matching name. If the employee's name from the Name column is found in the Group/Lists column, replace that name with the employee's Title from the Employee sheet. I then need this process to loop and continue through each column of the Groups/Lists sheet until all columns have completed. The end result should be that all names on the Groups/Lists sheet have been replaced with the corresponding Title found on the Employee sheet.

I hope someone can help me. I have looked high and low but haven't found a solution that will work for this.

VLookup will work, but again this is a very large workbook. The Employees sheet includes approximately 3000 employees and the Groups/Lists sheet has 3500 Groups or Lists. It's a bit large to upload so I've put some of the info into another sheet and uploaded. Thanks for your help!

Does this help?

Please Login or Register  to view this content.

Thanks for responding. When I run it, I get a cell mismatch error and when I Debug, it shows this:
error.JPG

I can't duplicate the error based on your sample. Try:

Please Login or Register  to view this content.

Correction, I had made an edit to accommodate slightly different data; I just ran again (As you originally wrote it) and it works BEAUTIFULLY! You are a MAD GENIUS!!! Thank you so much! I have working on this for weeks and unable to find a resolution until now!

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Last thing, it works through the 4 columns I sent as the example but not through the rest, so how do I extend this so that it applies to all 3520 columns? I'm looking at the script now, and I *think* it's the For x = 1 to 5 section but I'm not sure.

Great help, issue resolved. I can figure out how to push it through the rest of the sheet.

Maybe:

Please Login or Register  to view this content.

thanks for responding; I'm testing it now and will update once it completes.

I don't get any errors but the script never appears to complete running. This could be due to the large amount of data. I created a new test workbook and replaced the limited example of the Groups/Lists with the full list then ran the script. I get the turning circle to indicate that it's working through the data but it never goes anywhere. If I open any other window (such as this browser window) the turning circle goes away but then the workbook is "locked" in that I can't select it. It doesn't say "Not Responding" and is listed in the task manager as Running, but it does nothing. I've tried it a couple of times.

I assume that your lists are very large, and planting a VLOOKUP formula in large range will cause excel to behave in that manner. The code is working. It will take some time to complete. Depending on how many columns you have if you take that and multiply it by 3500, then yes you can go and have a coffee break. I know, it happens to me. There may be an alternative method, but I don't know of one.

That's all I needed to hear: Starbucks, here I come! Thank you again for all of your help.