Equation not quite what I was hoping for...

[HankJ]

Hello

I have a formula which is returning me #NAME?

The code is:

Range("FJ2:FJ" & LR_CompID).FormulaR1C1 = "=VLOOKUP(RC[-2]," & ("FQ" & LR_FP + 1 & ":FR" & LR_FQ) & ",2,FALSE)"

and the formula appears as:
=VLOOKUP(FH2,'FQ13':'FR15',2,FALSE)

Question: how do I paste without the ' either side of the range cells?

Many thanks

Hankj

 

[Eric W]

The main issue is that you're mixing the RC and the A1 style of referencing in the same formula. Try using one or the other and see what happens.

 

[HankJ]

The main issue is that you're mixing the RC and the A1 style of referencing in the same formula. Try using one or the other and see what happens.

Hi Eric W

Thanks for that. Sorted.

As an aside, I've always used FormulaR1C1 = to paste in a formula.

Is there a distinction between the types of statements to add different types of formula, RC v A1 styles?

Again thank you

HankJ

 

[Eric W]

There really isn't a difference between the two types, they both evaluate the same. Just depending on how you create the formula, and what the rest of your code looks like, I've found that creating the RC type is a bit easier. But I've done it both ways.

Glad to help.