Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Determine, cell has got reference.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Determine, cell has got reference.
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Determine, cell has got reference.
Sorry, that doesn't answer your question at all, will look later
Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Determine, cell has got reference.
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Determine, cell has got reference.
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 ... Sorry, that doesn't answer your question at all, will look later Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine button reference | Setting up and Configuration of Excel | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
HOW-TO: Determine if a cell contains a number vs. formula vs. reference | Excel Programming | |||
determine row reference from button | Excel Programming |