VLookup using r1c1 between sheets?

Hello All!

Having trouble with the proper syntax in VBA.

I have 2 sheets, PR and DA. I have already written a macro to import data into those sheets from two separate workbooks, format, etc, works perfectly.

I need vlookup to reference column B in PR to column B in DA and put the data from Column C in DA into column E in PR. The number of rows in column B in both sheets will vary depending on the other two workbooks, anywhere between 80 to 100 rows.

Any advice on how to do this?

Thanks!
CJ

There is more than one way to do this, but no matter how you do it I can't give the exact code without knowing what you have to start with. Here is one example that literally uses VLOOKUP:

Worksheets("PR").Cells(r, "E") = Application.WorksheetFunction.VLookup(Worksheets("PR").Cells(r, "B"), Worksheets("DA").Range("B:C"), 2)

where r is the row number on PR where you want to lookup/insert a value. The number of rows you have doesn't matter.

Another way using Find instead of VLOOKUP:

Worksheets("PR").Cells(R, "E") = Worksheets("DA").Cells(Worksheets("DA").Range("B:B").Find(Worksheets("PR").Cells(R, "B")).Row, "C")

The code can be streamlined a bit if I knew what module you were putting it in.

Thanks for the reply. Attached is the file with all personal data removed.

- Employee names were listed in column B on both sheets which is why most of those are blank in this file. I put a few fakes in there for testing.
- The original workbook data that was sent to the DA sheet was sorted by location code (column A), with names listed as First Last in column B.
- The original workbook data that was sent to the PR file was sorted alphabetically by Last Name, First Name (as you can see in the macro I removed the commas and reversed them so it would match the DA sheet's names)
- Both original workbooks had a ton of other data that wasn't needed so I had to delete multiple columns

Now that I look at this, it makes much more sense to copy columns C and D from sheet PR to sheet DA's corresponding names in Rows F and G (since I was going to eventually sort by location code anyways).

Thanks again for looking!