How to get reference to a range as string?

[jtan]

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.

 

[Guest]

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]

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

 

[Guest]

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