Using a variable in a formula

[gt93grad]

I can't get this statement to work with a varaible. "StageCostRange" is a variable. I can't figure out where the quotations and apostrophes should go.

ActiveCell.FormulaR1C1 = "=IFERROR('VLOOKUP(A7&B7, " & StageCostRange & " ,2,FALSE)',0)"

 

[gsistek]

I think you just need to remove the single quotes (apostrophes) before VLOOKUP and after FALSE).

 

[gt93grad]

Closer. This is what I have now:

ActiveCell.FormulaR1C1 = "=IFERROR('VLOOKUP(A7&B7, " & StageCostRange & " ,2,FALSE'),0)"

However, it's treating the A7&B7 as a literal, by inserting apostrophes: 'A7'&'B7'
That's preventing the lookup from working. How can I tell it that A7 and B7 and cells in the spreadsheet, and not literals?

 

[jasmith4]

Get rid of ALL the apostrophes.

 

[gt93grad]

Yeah, this is what I'm using, but it's still treating A7 and B7 as literals. I'm using no apostrophes now.

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(A7&B7, " & StageCostRange & " ,2,FALSE),0)"

 

[Jonmo1]

What is the value of StageCostRange ?
Can you post the code that gives that variable it's value?

Drop the R1C1 and just use .Formula

 

[jasmith4]

Yeah, this is what I'm using, but it's still treating A7 and B7 as literals. I'm using no apostrophes now.

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(A7&B7, " & StageCostRange & " ,2,FALSE),0)"

After executing this line of code, switch back to Excel, click in the formula bar (the whole formula should be selected), hit Ctrl-C to copy it, hit Esc to get out of edit mode, and paste the result here. That is, what is the formula that the cell ends up having?

 

[gt93grad]

Actually, after dropping R1C1, it seems to work now. Weird. Thanks.

 

[Jonmo1]

Glad to help, thanks for the feedback.

The reason it faild is because when you use FormulaR1C1, then it expects a formula written in R1C1 style references.
But you were giving it a formula written in A1 style.