# Find a value offset from active cell

1. ## 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?

2. ## Re: Find a value offset from active cell

Well, if you were doing it using a formula, you could use, for example,
Formula:
`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.``

3. ## 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. ## Re: Find a value offset from active cell

Originally Posted by jomili
Or could I just write it as "RC19"?
That would be simpler.

5. ## 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. ## Re: Find a value offset from active cell

You're welcome. Thanks for the rep.

#### Thread Information

##### Users Browsing this Thread

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

#### 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