+ Reply to Thread
Results 1 to 9 of 9

Negative relative reference in R1C1 Formula inside vlookup

  1. #1
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Negative relative reference in R1C1 Formula inside vlookup

    x = 2

    Set R = Range(Cells(2, x + 2), Cells(z, x + 4))
    Worksheets(1).Cells(2, x) = "=vlookup(RC[" & 1 - x & "]," & R.Address & ",2,0)"
    I am facing issue with the above code. I am intending to run this in a loop with x increments in steps of 2. If I manually give any value in place of RC[" & 1 - x & "] then it works which hints that only that part going wrong.

    Anyone can offer a quick help?

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,790

    Re: Negative relative reference in R1C1 Formula inside vlookup

    You are combining A1 and R1C1 references in that formula, which is why it fails. Try:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Quote Originally Posted by rorya View Post
    You are combining A1 and R1C1 references in that formula, which is why it fails. Try:
    Please Login or Register  to view this content.
    As I said, R.Address part works absolutely fine. I get error even if I replace that with some static range value but facing issue on 1-x part only

    Update: My bad. Somehow your suggestion worked. Trying to understand theory behind that how it happened though
    Last edited by contactfornitish; 06-11-2016 at 03:51 AM.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,790

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Quote Originally Posted by contactfornitish View Post
    Somehow your suggestion worked. Trying to understand theory behind that how it happened though
    I explained why in my last post.

  5. #5
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Quote Originally Posted by rorya View Post
    I explained why in my last post.
    Got stuck again. Can you help that what going wrong with below code

    Please Login or Register  to view this content.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,790

    Re: Negative relative reference in R1C1 Formula inside vlookup

    What is happening?

  7. #7
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Its giving Run-time error '1004', Application-Defined or Object-defined error and debug is taking to this line.

  8. #8
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Uploading the Macro enabled file
    https://drive.google.com/file/d/0B_r...ew?usp=sharing

    The pre-requisites are:
    C:\temp and D:\Ping folders need to be there and the below csv files would be in D:\ping which would be processed into a report at c:\temp
    https://drive.google.com/file/d/0B_r...ew?usp=sharing
    https://drive.google.com/file/d/0B_r...ew?usp=sharing

  9. #9
    Registered User
    Join Date
    05-11-2016
    Location
    New Delhi
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Negative relative reference in R1C1 Formula inside vlookup

    Just a space in last vlookup created whole headache

+ 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] Help needed on R1C1 formula reference
    By chococ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-18-2014, 09:08 AM
  2. [SOLVED] Formula R1C1 reference a column
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2014, 08:39 PM
  3. 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
  4. Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  5. Using Range Name reference as part of R1C1 Formula
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-14-2012, 01:32 PM
  6. Use Match or Lookup to create dynamic cell reference in VBA R1C1 formula
    By rmunsun1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-20-2012, 06:10 AM
  7. vlookup relative reference?!!??!!
    By rainxking in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2005, 09:05 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