Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Syntax filename rangename for vlookup formula
Hello,
I am trying to automate some tasks in vb (Excel 200 SP3) and need to set a range name to refer to another workbook and rangename to use in a vlookup formula. Dim Check As Variant Dim Result As Variant Dim Bus_Unit As Variant Dim Look_Table As Range Set Look_Table = Names("I:\OPA\Financials\DOO Macros.xls!AOP_Children").RefersToRange ... ... ... ... more code When it was simply Set Look_Table = Names("AOP_Children").RefersToRange with the range in the current workbook all works fine, but no luck when I try and refer to an external (opened) workbook. I am getting a runtime error 1004. Obviously something simple in my sintax but I cannot see it. File exists, range name exists and is open. Any help would be appreciated. Thanks Thomas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Syntax filename rangename for vlookup formula
Thomas,
TRy Look_Table= Workbooks(" Macros.xls")Worksheets("AOP_Children").Names("rang e_name").RefersToRange -- HTH RP (remove nothere from the email address if mailing direct) "Thomas" wrote in message ... Hello, I am trying to automate some tasks in vb (Excel 200 SP3) and need to set a range name to refer to another workbook and rangename to use in a vlookup formula. Dim Check As Variant Dim Result As Variant Dim Bus_Unit As Variant Dim Look_Table As Range Set Look_Table = Names("I:\OPA\Financials\DOO Macros.xls!AOP_Children").RefersToRange .. .. .. .. more code When it was simply Set Look_Table = Names("AOP_Children").RefersToRange with the range in the current workbook all works fine, but no luck when I try and refer to an external (opened) workbook. I am getting a runtime error 1004. Obviously something simple in my sintax but I cannot see it. File exists, range name exists and is open. Any help would be appreciated. Thanks Thomas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Syntax filename rangename for vlookup formula
Bob,
No luck. Still 1004 error. For the range I am referring to 1) File is open 2) File name is correct 3) Sheet name is correct (tred with and without) 4) Range name exists. The rest of the macro works fine if I move the range (both name and data) into the file I want the code to modify. Any help would be reallp appreciated. Thanks Thomas Day *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Syntax filename rangename for vlookup formula
Thomas,
Can you post all the code, I am not sure what you are doing exactly. -- HTH RP (remove nothere from the email address if mailing direct) "Thomas" wrote in message ... Bob, No luck. Still 1004 error. For the range I am referring to 1) File is open 2) File name is correct 3) Sheet name is correct (tred with and without) 4) Range name exists. The rest of the macro works fine if I move the range (both name and data) into the file I want the code to modify. Any help would be reallp appreciated. Thanks Thomas Day *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Syntax filename rangename for vlookup formula
Bob,
I tried coming at it from a different angle. Instead of defining the range name pointing to the other workbook, I have included the external reference directly in the vlookup and .... SUCCESS. Here is the code: @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ' 'Author: Thomas Day - DOO Office 'Date: 02 December 2004 'Data File: Working with Payroll Annual Leave Output File 'Part 4 of total end to end process Dim Check As Variant Dim Result As Variant Dim Bus_Unit As Variant Dim Look_Table As Range Range("A2").Select Set Bus_Unit = Range("A2") 'ActiveCell Set Check = ActiveCell.Offset(0, 1) Do While Not IsEmpty(Check.Value) '---------------------------------------------------------- 'Problem area ... now working Result = Application.VLookup(Check, _ Workbooks("DOO Macros.xls").Names("AOP_Children").RefersToRange, 4, False) '---------------------------------------------------------- If IsError(Result) Then Bus_Unit.Value = "Not Ops" 'erroe message Else Bus_Unit.Value = Result 'desired result End If Set Check = Check.Offset(1, 0) Set Bus_Unit = Bus_Unit.Offset(1, 0) Loop End Sub @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Thanks for your help Bob. Thomas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing For a RangeName | Excel Discussion (Misc queries) | |||
Confused with RangeName CellREfs | Excel Discussion (Misc queries) | |||
ActiveWorkbook.Protect password:=range("rangename") does not work | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel | |||
Concatenate RangeName for INDEX? | Excel Worksheet Functions |