Vlookup help

[Steph]

Hello. I can't seem to get the below formula to work. Conceptually, I need
the Col_Index_Num field in the vlookup formula to be the current column +
3 - the value in cell C2. When I run the line below, I get a Sub or
Function not Defined error on Column(). So I'm guessing Column() is a
worksheet function and not vba code. Any ideas on how I can switch around?
Thanks!

ActiveCell.Formula = _
"=VLOOKUP($A$4,Data!$A$7:$Q$1000," & Column() + 3 -
Range("C2").Value & ",0)"

 

[Die_Another_Day]

ActiveCell.Column + 3 - Range("C2")

HTH

Die_Another_Day

 

[Tom Ogilvy]

That is correct, column() is a worksheet formula.

One Possibility

ActiveCell.Formula = _
"=VLOOKUP($A$4,Data!$A$7:$Q$1000,Column() + 3 -" &
Range("C2").Value & ",0)"


or:

ActiveCell.Formula = _
"=VLOOKUP($A$4,Data!$A$7:$Q$1000," & _
activecell.column + 3 - Range("C2").Value & ",0)"