LOOKUP filename construction

[Guest]

This is one of those question where the answer is so obvious I cannot see it!!

The following is part of a larger macro:-


filedate=Range("F115".Value 'value is date in format dd/mm/yyyy
fileyear=Right(filedate,4)
lastyear=(fileyear-1)

lastyearfile="Planner-" & lastyear & ".xls"

Sheet ("Plan 1").Sheet
Range("E3").Select

ActiveCell.FormulaR1C1="LOOKUP(R[-1]C,'[lastyearfile]Plan 1'!R2C5:R3C20



It is the last line that contains an error as the 'lastyearfile' value is
not being identified. Debug shows it is carried through to this point.

I know it is to do with brackets and quotation marks - but my mind just
cannot see it

Any help appreciated

 

[Bob Phillips]

ActiveCell.FormulaR1C1="LOOKUP(R[-1]C,'[" & lastyearfile & "]Plan
1'!R2C5:R3C20"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks - Easy when you know how!

ActiveCell.FormulaR1C1="LOOKUP(R[-1]C,'[" & lastyearfile & "]Plan
1'!R2C5:R3C20"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

This is one of those question where the answer is so obvious I cannot see
it!!

The following is part of a larger macro:-


filedate=Range("F115".Value 'value is date in format dd/mm/yyyy
fileyear=Right(filedate,4)
lastyear=(fileyear-1)

lastyearfile="Planner-" & lastyear & ".xls"

Sheet ("Plan 1").Sheet
Range("E3").Select

ActiveCell.FormulaR1C1="LOOKUP(R[-1]C,'[lastyearfile]Plan 1'!R2C5:R3C20



It is the last line that contains an error as the 'lastyearfile' value is
not being identified. Debug shows it is carried through to this point.

I know it is to do with brackets and quotation marks - but my mind just
cannot see it

Any help appreciated