PDA

View Full Version : Solved: display range's "refer to"



TheAntiGates
09-28-2006, 05:49 PM
Is there a simple way to display a range's areas or formulas, as you see under menu item Insert/Name/Define ? I.e., the "refers to" string?

If not, would I need to parse and loop with regions and areas? Would I
- check .HasFormula and then grab .Formula? or .FormulaR1C1?
- take rng.areas.count and build it with semicolons if multiple?
- loop through that count with the "Items?" How do I address them? rng(1), rng(2) ?!
Use .Formula or .Text ?

Tell me it's already available, please!

Jacob Hilderbrand
09-28-2006, 06:32 PM
You can use Address to get that.


Range("A1").Address
Range("MyRange").Address

Jacob Hilderbrand
09-28-2006, 06:36 PM
Or if you wanted to see the formula and the resulting address:


Option Explicit

Sub NamesTest()

Dim N As Name

For Each N In ThisWorkbook.Names
MsgBox N.RefersTo
MsgBox Range(N.Name).Address
Next N

End Sub

TheAntiGates
09-28-2006, 07:09 PM
MsgBox N.RefersTo
MsgBox Range(N.Name).AddressOutstanding :thumb

Ken Puls
09-28-2006, 07:10 PM
Hi there,

If you're just looking to show the formula in a cell, I have an article to do that here. (http://www.excelguru.ca/node/26)

HTH,