How to get reference to a range as string?

[jtan@alum.mit.edu]

I can get a reference to a cell as string with function CELL("address",
A1), which will return the string $A$1. How do I get a reference to a
range, for instance, A1:A5?

Thanks.

[Gary Brown]

Assuming you're taking about macros...
selection.address or range("A1:A5").address would produce a string of
$A$1:$A$5.
HTH,
Gary Brown



"jtan@alum.mit.edu" wrote:

> I can get a reference to a cell as string with function CELL("address",
> A1), which will return the string $A$1. How do I get a reference to a
> range, for instance, A1:A5?
>
> Thanks.
>
>

[jtan@alum.mit.edu]

Thanks. I'm actualy writing it as a function, say Addr(range). It works
only for the range in the current sheet. If I enter Addr(Sheet2!A1:A5)
on a cell in Sheet1 , range.address only returns $A$1:$A$5 without the
sheet name. How do I write the Addr() so it adds the sheetname! only if
the input range is on another sheet.
Function Addr(r As Range)
Addr = r.Address
End Function

[Gary Brown]

Function addr(r As Range) As String
Application.Volatile

If ActiveSheet.Name <> r.Worksheet.Name Then
addr = r.Worksheet.Name & "!" & r.Address
Else
addr = r.Address
End If

End Function

hth,
Gary Brown


"jtan@alum.mit.edu" wrote:

> Thanks. I'm actualy writing it as a function, say Addr(range). It works
> only for the range in the current sheet. If I enter Addr(Sheet2!A1:A5)
> on a cell in Sheet1 , range.address only returns $A$1:$A$5 without the
> sheet name. How do I write the Addr() so it adds the sheetname! only if
> the input range is on another sheet.
> Function Addr(r As Range)
> Addr = r.Address
> End Function
>
>