VBA - Syntax filename rangename for vlookup formula

[Thomas]

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

 

[Bob Phillips]

Thomas,

TRy

Look_Table= Workbooks("
Macros.xls")Worksheets("AOP_Children").Names("range_name").RefersToRange

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Thomas]

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

 

[Bob Phillips]

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]

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