+ Reply to Thread
Results 1 to 4 of 4

VLookup using r1c1 between sheets?

  1. #1
    Registered User
    Join Date
    09-27-2018
    Location
    Ohio
    MS-Off Ver
    16
    Posts
    9

    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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,364

    Re: VLookup using r1c1 between sheets?

    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:

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    The code can be streamlined a bit if I knew what module you were putting it in.

  3. #3
    Registered User
    Join Date
    09-27-2018
    Location
    Ohio
    MS-Off Ver
    16
    Posts
    9

    Re: VLookup using r1c1 between sheets?

    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!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-27-2018
    Location
    Ohio
    MS-Off Ver
    16
    Posts
    9

    Re: VLookup using r1c1 between sheets?

    original post deleted, strike thru didnt work so just cut out the crap

    Nevermind. I figured it out.

    Please Login or Register  to view this content.
    Again, thanks for taking the time to look!
    Last edited by pizzaguy4487; 02-11-2021 at 09:18 PM. Reason: figured it out

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] R1C1 Vlookup with Variables to reference column numbers
    By sethrath in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2020, 08:38 PM
  2. [SOLVED] Vlookup from another workbook Using R1C1 Notation
    By sgoodrich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-20-2020, 10:35 AM
  3. Negative relative reference in R1C1 Formula inside vlookup
    By contactfornitish in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-14-2016, 04:39 PM
  4. Using R1C1 formula in VBA changes all references from CELL("address") function to R1C1?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2014, 04:17 PM
  5. Vlookup HELP R1C1 variable
    By toutai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2014, 04:41 AM
  6. R1C1 cell reference in Vlookup...with wildcard?
    By musicman715 in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 08-30-2013, 01:45 PM
  7. vlookup and R1C1 HELP!
    By plain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-29-2006, 01:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1