formulas in range with references outside the range

[faffo1980]

Hi all,
I would like to know which is, in your opinion, the best way to discover if
a cell inside a range object contains formulas referring to cells outside the
range (that is very probable).
I'm writing an Excel 2003 Add-In in c# language. .NET 2.0 framework

Thanks for your help,

Fabrizio

 

[Jacob Skaria]

Use Application.Intersect as below
...
ActiveCell.Precedents or your cell reference.Precedents


Dim ws As Worksheet, rngTemp As Range
Set ws = ActiveSheet

Set rngTemp = ws.Range("A1:H16")

If Not Application.Intersect(ActiveCell.Precedents, rngTemp) Is Nothing Then
MsgBox "Reference within Range"
End If



If this post helps click Yes

 

[Patrick Molloy]

using the cell's
..Precedents.Cells
property with an Intersect() returns either a range if the precedent cells
are inside or nothign if they're outside the range. heres' an example.

Sub CheckReferences()
Dim found As Range
Dim source As Range
Dim cell As Range
Set source = Range("F9:I23")
Set found = source.SpecialCells(xlCellTypeFormulas)

If Not found Is Nothing Then
For Each cell In found.Cells
If Intersect(cell.Precedents.Cells, Range("F9:I23")) Is Nothing
Then
MsgBox cell.Address & " prec outside"
Else
MsgBox cell.Address & " prec inside"
End If
Next
End If
End Sub

 

[faffo1980]

Thanks Jakob and Patrick,
I found your reply very useful.

Regards,

faffo1980