LOOKUP filename construction
 

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

LOOKUP filename construction
 

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)

"SyZyGy" wrote in message
...
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

LOOKUP filename construction - Thanks - Problem solved
 

Thanks - Easy when you know how!

"Bob Phillips" wrote:

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)

"SyZyGy" wrote in message
...
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