+ Reply to Thread
Results 1 to 6 of 6

Find a value offset from active cell

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,467

    Find a value offset from active cell

    In my macro I'm using the below formula:
    Please Login or Register  to view this content.
    The RC[-33alue is a guess. It's the "Employee ID" column, sometimes named "EmplID", sometimes named "ID", and sometimes named something outlandish like "User Number". It can also exist in a different location, so it might be in D, G, Z, really anywhere in the sheet.

    I'm starting my process by picking one cell to start with for all the formulas and columns I need to put in. I've got my entire macro working fine, but want to fine tune this particular formula.

    My question is, how would I find, for instance, the value "ID" or "Number" in a column and determine the offset from my active cell to that column, to capture it as a variable to insert in my formula?
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: Find a value offset from active cell

    Well, if you were doing it using a formula, you could use, for example,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To convert that into code, you'd have something like

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,467

    Re: Find a value offset from active cell

    Thanks Trevor, that gets me started. Where I lose my mind is in the relative references. For instance, the formula in question is in (roughly, it changes depending on which invoice I'm working on) the 46th column from A. My "home" cell is in Col 23, and the value I want to use is in Col 19. So, if I use
    Please Login or Register  to view this content.
    it's going to give me the absolute column in the workbook, when what I need (I think) is the relative position from the 46th column, so if it gave me 19, I'd need to write it as RC[-27], because 46-19=27. So would I instead perform the calculation and use the result as a variable?

    Or could I just write it as "RC19"?

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

    Re: Find a value offset from active cell

    Quote Originally Posted by jomili View Post
    Or could I just write it as "RC19"?
    That would be simpler.
    Rory
    I drink, and I know things

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,467

    Re: Find a value offset from active cell

    Yep, simple is the way to go. I'm now using the variable "lCol" in place of RC[-33] and life is going along beautifully. Trevor fixed me up!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: Find a value offset from active cell

    You're welcome. Thanks for the rep.

+ 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] Offset formula using active cell
    By Jed_c in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-13-2020, 04:43 AM
  2. Offset formula using active cell
    By Jed_c in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 04-13-2020, 01:31 AM
  3. [SOLVED] Cannot paste formula into offset active cell
    By icyrius in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-09-2018, 11:52 AM
  4. copying cell values from an active row using the offset function
    By Maxwelll in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2018, 01:27 PM
  5. [SOLVED] Userform textbox to reference active cell with offset
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 12-15-2013, 01:38 AM
  6. [SOLVED] Userform to add data to active cell offset
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 12-14-2013, 07:58 PM
  7. Running a different Active.Cell Offset.....
    By Kirtman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 05:52 AM

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