Extracting 'exact' local references from formula
 

Is there any way to get the exact string representing the local
references as the user entered it in the formula?

For example:

Cell A1 in Sheet1: "=Sheet2!G66+Sheet1!B$7-$A9"

Getting DirectPrecedents yields:
"$B$7" and "$A$9" for Address

What I *really* want is:
"Sheet1!B$7" and "$A9"

The frustrating thing is when I double click in the cell (edit mode)
Excel will colour these exact strings - But I can't find anyway of
getting to them in a macro :-(

Any help appreciated!

Antony

Extracting 'exact' local references from formula
 

Antony,

A bit nasty, but is this of any use

Set rng = ActiveCell.DirectPrecedents
For Each area In rng.Areas
Debug.Print area.Parent.Name & "!" & area.Address
Next area


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ant Bewes" wrote in message
om...
Is there any way to get the exact string representing the local
references as the user entered it in the formula?

For example:

Cell A1 in Sheet1: "=Sheet2!G66+Sheet1!B$7-$A9"

Getting DirectPrecedents yields:
"$B$7" and "$A$9" for Address

What I *really* want is:
"Sheet1!B$7" and "$A9"

The frustrating thing is when I double click in the cell (edit mode)
Excel will colour these exact strings - But I can't find anyway of
getting to them in a macro :-(

Any help appreciated!

Antony

Extracting 'exact' local references from formula
 

Thanks for the heads-up but I will need to preserve the nature of the
users reference, such as:

$A1 / A$1 / $A$1 or
R1C[1] / R[1]C1 / R[1]C[1] etc

Ant


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Extracting 'exact' local references from formula
 

Doesn't that do so?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ant" wrote in message
...

Thanks for the heads-up but I will need to preserve the nature of the
users reference, such as:

$A1 / A$1 / $A$1 or
R1C[1] / R[1]C1 / R[1]C[1] etc

Ant


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Extracting 'exact' local references from formula
 

Doesn't that do so?

um.. no!

This was the approach I originally took. I've actually solved the
bigger problem by a different means now - as ever! VBA is a real
obstacle course...