Vlookup help
 

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

Vlookup help
 

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

HTH

Die_Another_Day
Steph wrote:
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)"

Vlookup help
 

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


--
Regards,
Tom Ogilvy



"Steph" wrote in message
...
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)"