How To Dynamically Refer To A Cell In A Macro Formula

[ChrisF79]

Greetings:

I want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...

Instead of using =vlookup($A2,Sheet1A1:B50,2,false)

How do I replace the $A2 part with some code that says, "the cell to the left of here..."

[Shijesh Kumar]

There are many ways to approach this..

one is

activecell.offset( 0 , -1)

[ChrisF79]

There are many ways to approach this..
activecell.offset( 0 , -1)

I did try =vlookup(activecell.offset(0,-1),Sheet1......

It didn't work out though.

[DonkeyOte]

Please post your code.

[ChrisF79]

ActiveCell.FormulaR1C1 = _
"=vlookup(Activecell.Offset(0,-1),'[Vlookup Reference.xlsx]Items'!$A:$K,2,false)"

[DonkeyOte]

I meant your code in full...

However if entering a formula in R1C1 format you must obviously use R1C1 notation:

Please Login or Register  to view this content.

Also please post your code in CODE tags (use # symbol)

[ChrisF79]

Please Login or Register  to view this content.

It works! Thanks so much. Now I understand the R1C1 notation better.

[royUK]

Donkey & I have both requested that you use Code Tags, please do so then mark the thread solved