Using variables with a formula

[gt93grad]

Trying to use the ModelType variable to define an active range. It basically says to create "LinksFGsLookup" for a range on the "LinksFGs" worksheet. Sometimes I want to use the LinksFGs worksheet, but sometimes it's call some other form of LinksXXX. Given that ModelType is set to "FGs", why does the second line work, but the first does not? The first does not create a range name. No error, but it doesn't create a range.

ActiveWorkbook.Names.Add Name:="Links" & ModelType & "Lookup", RefersToR1C1:= _
"='Links[" & ModelType & "]'!R6C1:R" & LastRow & "C5"

ActiveWorkbook.Names.Add Name:="Links" & ModelType & "Lookup", RefersToR1C1:= _
"='LinksFGs'!R6C1:R" & LastRow & "C5"

 

[JoeMo]

Try removing the brackets from:
"='Links[" & ModelType & "]'!R6C1:R" & LastRow & "C5"

 

[gt93grad]

Try removing the brackets from:
"='Links[" & ModelType & "]'!R6C1:R" & LastRow & "C5"

Yep, that did it. Another example had the brackets, but maybe they were just using them to highlight the variable area. Thanks.

 

[gt93grad]

Here's a similar problem, but I can't get this one to work either. "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)"

 

[JoeMo]

Here's a similar problem, but I can't get this one to work either. "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)"

If StageCostRange is a range object try:

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

 

[gt93grad]

Seems to work, but now it's treating A7&B7 as a literal, and resulting in this. How do I tell it that A7 and B7 and cells in the worksheet?

=IFERROR(VLOOKUP('A7'&'B7', StageCostFGs,2,FALSE),0)

 

[JoeMo]

Seems to work, but now it's treating A7&B7 as a literal, and resulting in this. How do I tell it that A7 and B7 and cells in the worksheet?

=IFERROR(VLOOKUP('A7'&'B7', StageCostFGs,2,FALSE),0)

Copy the formula from your browser exactly as I posted it. There are no apostrophes (') in it. You said the range variable was StageCostRange, now you're showing StageCostFGs - which is it???

 

[gt93grad]

Maybe I copied the wrong one just then. In any case, I remove R1C1 from the statement and it seems to work now. It didn't like "ActiveCell.FormulaR1C1" for some reason.

 

[Christina594]

Seems to work, but now it's treating A7&B7 as a literal, and resulting in this.