Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
intersect named range in different workbook
Hi,
I am trying to check if a value is in a named range on a different workbook. I have created a named range in the current workbook that is linked to a named range in a different workbook, but when I try to execute the following function all I get is an error (Run-time error '1004': Method 'Range ' of object '_Global' failed. Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(ActiveSheet.Range("TA"),_ Workbooks("Calculations.xlsm").Sheets(29).Range("J obs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function I have also tried, with the same results: Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(Range("TA"), Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function and Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(TA, Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function which has a similar error (Run-time error '1004': Method 'Intersect' of object '_Application' failed) Any idea where I am going wrong? Thanks, Jason |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
intersect named range in different workbook
Checking whether two ranges intersect has nothing to do with determining a cell value. Also, for two ranges to intersect they must be on the same sheet. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jason" wrote in message Hi, I am trying to check if a value is in a named range on a different workbook. I have created a named range in the current workbook that is linked to a named range in a different workbook, but when I try to execute the following function all I get is an error (Run-time error '1004': Method 'Range ' of object '_Global' failed. Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(ActiveSheet.Range("TA"),_ Workbooks("Calculations.xlsm").Sheets(29).Range("J obs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function I have also tried, with the same results: Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(Range("TA"), Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function and Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(TA, Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function which has a similar error (Run-time error '1004': Method 'Intersect' of object '_Application' failed) Any idea where I am going wrong? Thanks, Jason |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
intersect named range in different workbook
As a followup to my problem, which I got around by doing the following;
Changed: Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(ActiveSheet.Range("TA"),_ Workbooks("Calculations.xlsm").Sheets(29).Range("J obs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function To: Function SearchFor(forLU As Range) As Boolean Dim Frng As Range Dim LU As String LU = forLU.Text If LU < vbNullString Then LU = forLU.Value Set Frng = Workbooks("Calculations.xlsm").Sheets("Lists").Ran ge("A41:A211")_ .Find(what:=LU, LookIn:=xlValues, lookat:=xlWhole) If Not Frng Is Nothing Then SearchFor = True Else SearchFor = False End If End If End Function Thanks for your response Jim. On Fri, 12 Oct 2007 17:55:57 +0000, Jason wrote: Hi, I am trying to check if a value is in a named range on a different workbook. I have created a named range in the current workbook that is linked to a named range in a different workbook, but when I try to execute the following function all I get is an error (Run-time error '1004': Method 'Range ' of object '_Global' failed. Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(ActiveSheet.Range("TA"),_ Workbooks("Calculations.xlsm").Sheets(29).Range("J obs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function I have also tried, with the same results: Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(Range("TA"), Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function and Function WithinJobRange(TA As Range) As Boolean If Application.Intersect(TA, Range("Jobs")) Is Nothing Then WithinJobRange = False Else WithinJobRange = True End If End Function which has a similar error (Run-time error '1004': Method 'Intersect' of object '_Application' failed) Any idea where I am going wrong? Thanks, Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking to a named range in another workbook | Excel Discussion (Misc queries) | |||
#REF on Links to Named Range in External Workbook | Excel Discussion (Misc queries) | |||
Named Range in ANOTHER workbook | Excel Discussion (Misc queries) | |||
Named Range in ANOTHER workbook | Excel Discussion (Misc queries) | |||
Named Range in another workbook | Excel Worksheet Functions |