Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate Formula with Named Ranges does not work if Workbook isalready open
I have a VB app that controls an Excel template (opens it, runs code
against it, closes it). I have a statement in the code that uses Evaluate with an Excel formula that contains named ranges. Normally, the Excel template is opened by the VB code, the workbook is assigned to an object variable and I go from there. If the workbook is already open, I simply use GetObject to assign the open workbook to the object variable, then proceed as usual. If the code opens the workbook, my formula works just fine: CurrentColumn = DataSheet.Range(Evaluate("=ADDRESS(MATCH(""Total Surveys Sent"",Customer_Satisfaction, 0)+ROW(Customer_Satisfaction)-1,MATCH(""" & CurrentMonth & """,Data_Month,0)+COLUMN(Data_Month)-1)")).Column If I just grab the existing workbook, the formula fails. I've discovered from some testing that it's the named ranges that are tripping up. Is there something about named ranges I need to know about to make these formulas work with workbooks that are already open? tod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Phantom named ranges in a workbook? | Excel Discussion (Misc queries) | |||
Linking to named ranges in another workbook | Excel Discussion (Misc queries) | |||
SQL query a workbook - too many named ranges? | Excel Programming | |||
Help to adapt Formula syntax to work with Dynamic Named Ranges | Excel Worksheet Functions | |||
Workbook not recognising named ranges | Excel Programming |