intersect named range in different workbook

[Jason]

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("Jobs")) 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

 

[Jim Cone]

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("Jobs")) 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

 

[Jason]

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("Jobs")) 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").Range("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.