Getting the value of .FormulaR1C1 help please

[jecruz]

Hi Everyone,

My question is about this:

ActiveSheet.Cells(i, 106).FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[Lookup Reference.xls]Sheet1'!R2C1:R11C2,2,FALSE)"

The output in excel is the formula itself. How can it be just the value retrieved from the lookup? I guess what I need is the syntax for vba.

Thanks a lot in advance. :D

 

[Biz]

Can you tell what does vlookup formula look like without R1C1 reference?

Biz

 

[jecruz]

=VLOOKUP(CX2,'[Lookup Reference.xls]Sheet1'!$A$2:$B$11,2,FALSE)

But i need this formula for rows 2 to 43071 thus the R1C1 reference.

Thanks.

 

[Biz]

Try this

Sub Test()
Range("DB2:DB43071").Formula = "=VLOOKUP(CX2,'[Lookup Reference.xls]Sheet1'!$A$2:$B$11,2,FALSE)"
End Sub

Does it help?

Biz

 

[AlphaFrog]

With Range("DB2:DB43071")
    .FormulaR1C1 = "=VLOOKUP(RC[-4],'[Lookup Reference.xls]Sheet1'!R2C1:R11C2,2,FALSE)"
    .Value = .Value
End With

 

[jecruz]

Exactly what I needed. Thanks a lot guys. :D