vlookup woes

[QB]

I coded the following, which worked beautifully in Excel 2003

Range("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC10,Configuration!R1C1:R" &
lstRowConfig & "C2,2,FALSE)"

Now however, in 2007 in get in the cell
=VLOOKUP(RC10,Configuration!R1C1:R10C2,2,FALSE)

but it does not actually return a value, it jsut displays the formula????
In 2003 it returned the value and everything worked like a charm?

Any ideas why it no longer works and how to fix it.

Thank you,

QB

 

[Mike H]

Hi,

Nothing has changed in E2007 that would cause this, I suspect the cell into
which your putting the formula may be formatted as text. Try this

With Range("L2")
.NumberFormat = "General"
.FormulaR1C1 = "=VLOOKUP(RC10,Configuration!R1C1:R" & _
lstRowConfig & "C2,2,FALSE)"
End With

Mike

 

[Jacob Skaria]

The below works both in 2003 and 2007

-Make sure you have the sheet named Configuration
-Also check whether the cell is formatted as 'General' and not as 'Text'

lstRowConfig = 2
Range("L2").FormulaR1C1 = "=VLOOKUP(RC10,Configuration!R1C1:R" & _
lstRowConfig & "C2,2,FALSE)"

If this post helps click Yes