Somewhere on my worksheet is a single Shape. It is a line with an arrowhead
at one end. The line begins in one cell and ends (arrow head side) in
another cell. How can a macro determine the address of these two cells?

If it matters, the line was drawn by Formula Auditing.

Thanks
--
Gary''s Student - gsnu200909

I've been looking at your problem from when you first posted it... yes, the
Formula Auditing does seem to matter. A normal line has a TopLeftCell and a
BottomRightCell property which you can reference to get their Address
properties; however, lines drawn by the Formula Auditing appear to be
different in that they do not have these properties. I'm still looking (on
and off) at some ideas on how do solve your problem though.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Somewhere on my worksheet is a single Shape. It is a line with an
arrowhead
at one end. The line begins in one cell and ends (arrow head side) in
another cell. How can a macro determine the address of these two cells?

If it matters, the line was drawn by Formula Auditing.

Thanks
--
Gary''s Student - gsnu200909

I also have been looking at your related post from yesterday, and have at
least the start of a solution. Try this:

Sub FindPrecedents()
'Declare local variables
Dim StartWS As Worksheet, RxRng As Range
Dim c As Range, d As Range
Dim HitCount As Long, NewSht As Worksheet, LastRx As String
On Error Resume Next
'Store the starting activesheet and activecell
Set StartWS = ActiveSheet
'Find all the cells with formulas on the active sheet
Set RxRng = ActiveCell.SpecialCells(xlCellTypeFormulas, 23)
'Add a new worksheet to the current workbook at the end
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NewSht = ActiveSheet
'Use HitCount& to set the output row. Will add 1 each time,
'so set to 3 initially to begin output on row 4.
HitCount& = 3
'Always start by returning to StartWS.
StartWS.Activate
For Each c In RxRng
Set d = c.NavigateArrow(True, 1)
'If there is no precedent tracing arrow, NavigateArrow returns the selected
cell.
If Intersect(d, c) Is Nothing Then
HitCount& = HitCount& + 1
NewSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name
NewSht.Cells(HitCount&, 2).Value = "'" & c.Address
NewSht.Cells(HitCount&, 3).Value = "'" & c.Formula
End If
Next c
'Done. Clean up. Add headings and resize all columns on NewSht.
NewSht.Activate
NewSht.Cells(3, 1).Value = "Sheet"
NewSht.Cells(3, 2).Value = "Cell"
NewSht.Cells(3, 3).Value = "Formula"
NewSht.Cells.Select
NewSht.Cells.EntireColumn.AutoFit
NewSht.Cells(1, 1).Value = "Precedent tracing for " & StartWS.Name
'Free object variables.
Set NewSht = Nothing
Set StartWS = Nothing
End Sub

As you can see, it uses NavigateArrows to check is a cell has a
precedent-tracing arrow. If there is an arrow, the first precedent cell is
returned. If there is no arrow, the cell being tested is returned.

Hope this helps,

Hutch

"Gary''s Student" wrote:

Somewhere on my worksheet is a single Shape. It is a line with an arrowhead
at one end. The line begins in one cell and ends (arrow head side) in
another cell. How can a macro determine the address of these two cells?

If it matters, the line was drawn by Formula Auditing.

Thanks
--
Gary''s Student - gsnu200909

Excellent Tom... thanks for posting your code... the NavigateArrows property
was a new one for me. Using it, I posted a simple function back to Gary''s
Student in my sub-thread which appears to do what he originally asked for
(given my limited testing on it); namely, a Boolean function to indicate
whether Precedent Tracing was turned on for a cell or not.

--
Rick (MVP - Excel)


"Tom Hutchins" wrote in message
...
I also have been looking at your related post from yesterday, and have at
least the start of a solution. Try this:

Sub FindPrecedents()
'Declare local variables
Dim StartWS As Worksheet, RxRng As Range
Dim c As Range, d As Range
Dim HitCount As Long, NewSht As Worksheet, LastRx As String
On Error Resume Next
'Store the starting activesheet and activecell
Set StartWS = ActiveSheet
'Find all the cells with formulas on the active sheet
Set RxRng = ActiveCell.SpecialCells(xlCellTypeFormulas, 23)
'Add a new worksheet to the current workbook at the end
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NewSht = ActiveSheet
'Use HitCount& to set the output row. Will add 1 each time,
'so set to 3 initially to begin output on row 4.
HitCount& = 3
'Always start by returning to StartWS.
StartWS.Activate
For Each c In RxRng
Set d = c.NavigateArrow(True, 1)
'If there is no precedent tracing arrow, NavigateArrow returns the
selected
cell.
If Intersect(d, c) Is Nothing Then
HitCount& = HitCount& + 1
NewSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name
NewSht.Cells(HitCount&, 2).Value = "'" & c.Address
NewSht.Cells(HitCount&, 3).Value = "'" & c.Formula
End If
Next c
'Done. Clean up. Add headings and resize all columns on NewSht.
NewSht.Activate
NewSht.Cells(3, 1).Value = "Sheet"
NewSht.Cells(3, 2).Value = "Cell"
NewSht.Cells(3, 3).Value = "Formula"
NewSht.Cells.Select
NewSht.Cells.EntireColumn.AutoFit
NewSht.Cells(1, 1).Value = "Precedent tracing for " & StartWS.Name
'Free object variables.
Set NewSht = Nothing
Set StartWS = Nothing
End Sub

As you can see, it uses NavigateArrows to check is a cell has a
precedent-tracing arrow. If there is an arrow, the first precedent cell is
returned. If there is no arrow, the cell being tested is returned.

Hope this helps,

Hutch

"Gary''s Student" wrote:

Somewhere on my worksheet is a single Shape. It is a line with an
arrowhead
at one end. The line begins in one cell and ends (arrow head side) in
another cell. How can a macro determine the address of these two cells?

If it matters, the line was drawn by Formula Auditing.

Thanks
--
Gary''s Student - gsnu200909