formulas in range with references outside the range

  • Thread starter Thread starter faffo1980
  • Start date Start date
F

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
 
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
 
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
 
Back
Top