Hi, Kris here
I want to know how to determine wheather a cell in a worksheet has got
reference to other workshhet or reference in the same worksheet.

Thanks

Hi Kris,

Sub Test()
Dim cel As Range, ar As Range
Dim rDeps As Range, rDirDeps As Range, rDirPrecs As Range

Set cel = Range("B2")

On Error Resume Next
Set rDeps = cel.Dependents
Set rDirDeps = cel.DirectDependents
Set rDirPrecs = cel.DirectPrecedents
On Error GoTo 0

If Not rDeps Is Nothing Then
For Each ar In rDeps.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If
If Not rDirDeps Is Nothing Then
For Each ar In rDirDeps.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If

If Not rDirPrecs Is Nothing Then
For Each ar In rDirPrecs.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If

End Sub

Regards,
Peter T


"kris" <(E-Mail Removed)> wrote in message
news:BD1CC184-4618-4046-9D47-(E-Mail Removed)...
> Hi, Kris here
> I want to know how to determine wheather a cell in a worksheet has got
> reference to other workshhet or reference in the same worksheet.
>
> Thanks

Sorry, that doesn't answer your question at all, will look later

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> Hi Kris,
>
> Sub Test()
> Dim cel As Range, ar As Range
> Dim rDeps As Range, rDirDeps As Range, rDirPrecs As Range
>
> Set cel = Range("B2")
>
> On Error Resume Next
> Set rDeps = cel.Dependents
> Set rDirDeps = cel.DirectDependents
> Set rDirPrecs = cel.DirectPrecedents
> On Error GoTo 0
>
> If Not rDeps Is Nothing Then
> For Each ar In rDeps.Areas
> Debug.Print ar.Address
> For Each c In ar.Cells
> Debug.Print , c.Address
> Next
> Next
> End If
> If Not rDirDeps Is Nothing Then
> For Each ar In rDirDeps.Areas
> Debug.Print ar.Address
> For Each c In ar.Cells
> Debug.Print , c.Address
> Next
> Next
> End If
>
> If Not rDirPrecs Is Nothing Then
> For Each ar In rDirPrecs.Areas
> Debug.Print ar.Address
> For Each c In ar.Cells
> Debug.Print , c.Address
> Next
> Next
> End If
>
> End Sub
>
> Regards,
> Peter T
>
>
> "kris" <(E-Mail Removed)> wrote in message
> news:BD1CC184-4618-4046-9D47-(E-Mail Removed)...
> > Hi, Kris here
> > I want to know how to determine wheather a cell in a worksheet has got
> > reference to other workshhet or reference in the same worksheet.
> >
> > Thanks
>
>

Cell have a "Precedents" collection, but this doesn't include references to
other sheets, only the sheet the cell is in.

It would be tricky to determine absolutely (using VBA) whether there are any
references to other sheets - you will have to take into account such things
as named ranges. And of course any use of INDIRECT() will also pose a
problem.

I'd be interested in seeing what others might suggest though.

Tim


"kris" <(E-Mail Removed)> wrote in message
news:BD1CC184-4618-4046-9D47-(E-Mail Removed)...
> Hi, Kris here
> I want to know how to determine wheather a cell in a worksheet has got
> reference to other workshhet or reference in the same worksheet.
>
> Thanks

Tricky! Tim has outlined the basic problem, namely the Dependents &
Precedents collections only include cells on same sheet. That implies a lot
of tedious parsing of cell formulas throughout the entire wb is required
looking for cell references, not to mention use of Indirect, Named formulas
and no doubt elsewhere such as CF's.

I had assumed, unfortunately wrongly it seems, that because Trace Dependents
& Precedents indicates references on other sheets there would be a simpler
way.

Regards,
Peter T

PS, I haven't checked the archives but this surely must have come up before.


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Sorry, that doesn't answer your question at all, will look later
>
> Regards,
> Peter T
>