Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs Filename:=filename, FileFormat:=xlCSV | Excel Programming | |||
How do I lookup large number of workbooks (by filename) for data? | Excel Programming | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
range construction | Excel Programming | |||
formula construction II | Excel Programming |