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?

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.

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"?

Originally Posted by jomili

Or could I just write it as "RC19"?

That would be simpler.

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!

You're welcome. Thanks for the rep.