Is there any function wich returns a range as text?

[Irada Shamilova]

I need a function that will return the address of a specified Range as
text. Can anybody help me?

For example:
=Function(E13:H20) should return E13:H20 (not !VALUE)

And if in the future I need to change my Range I would just adjust th
frame (after entering into formula by pressing F2). If I take the rang
in the brackets like "E13:H20"(make it to be text), then I wouldn't se
the frame around my range & everytime afterwards I should manuall
change the range.

Why I need this? I have a list of templates for my report. I hav
written the macros which takes one of templates & inserts it in th
report then filles with numbers. So, it's very useful when I see th
whole range by entering into formula (F2) - the range will be take
into a frame & I can play with the frame not row/column numbers .

So far I use simple formula "=(E13:H20)", but it returns "!VALUE" (
want it to return "E13:H20")

Thanks in advance

 

[Frank Kabel]

Hi
maybe a UDF like
public function rng_address(rng as range)
rng_address=rng.address
end function

 

[hgrove]

Frank Kabel wrote...

maybe a UDF like
public function rng_address(rng as range)
rng_address=rng.address
end function ...
-----Original Message----- ...
For example:
=Function(E13:H20) should return E13:H20 (not !VALUE) ...
Why I need this? I have a list of templates for my report. I have
written the macros which takes one of templates & inserts it in
the report then filles with numbers.

...

If you, the OP, are using macros, you could just use the Addres
property for any range objects you use. No need for a udf. If you wan
to use this in worksheet cells, still no need for a udf.

=MID(CELL("Address",(A1,YourRangeReferenceHere)),6,9)

or

=SUBSTITUTE(MID(CELL("Address"
(A1,YourRangeReferenceHere)),6,9),"$",""